Hi,
I want to calculate difference from previous quarter to next quarter whereas I have no date field in this table. I have only year and quarter column. A column shows all quarters like Q1, Q2, Q3 and Q4 and other columns shows the value of the specific quarter based on an indicators. I used a measure like this:
IF (
FIRSTNONBLANK ( 'Calendar'[Quartername], 1 ) = "Q1",
SUM ( 'QuarterlyProgress'[Progress] ),
SUM ( 'QuarterlyProgress'[Progress] )
- CALCULATE ( SUM ( 'QuarterlyProgress'[Progress] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) )
)
Noted that Calendar[Date] is another table field but there is no way to make relationship between quarterly progress table and calendar table. I have attached the screenshot. Please help me to calculate dynamically.
Best regards,
Kaysar
Hi Kaysar,
You simply cannot use time intelligence functions without a proper date field. I suggest you create a date field in a dd/mm/yyyy or mm/dd/yyyy format from your Year and Quarter fields.
Mynda
Hi Mynda,
Kaysar again with the issue of difference between previous quarter, next quarter and current quarter. I have created date field as you suggested and use the following calculation to resolve the issue but there is showing aggregated 4 quarters total instead of difference between last quarter and current quarter. The attached screenshot showing the grand total figure and quarter difference figure is same.
=IF (
FIRSTNONBLANK ( '10QuarterlyReportProgressPC'[Quarter], 1 ) = "Q1",
SUM ('10QuarterlyReportProgressPC'[Progress] ),
SUM( '10QuarterlyReportProgressPC'[Progress] )
- CALCULATE ( SUM ( '10QuarterlyReportProgressPC'[Progress] ), PREVIOUSQUARTER ( '10QuarterlyReportProgressPC'[Start_QuarterYear] ) )
)
I have tried another way:
=var _thisQ = TOTALQTD(
[Sum of Progress], '10QuarterlyReportProgressPC'[Start_QuarterYear] )
var _lastQ = TOTALQTD(
[Sum of Progress] , PREVIOUSQUARTER( '10QuarterlyReportProgressPC'[Start_QuarterYear]),
VALUES('10QuarterlyReportProgressPC'[Quarter]) )
RETURN
IF(_lastQ = 0,
BLANK(),
_thisQ - _lastQ
)
But the result is same. Only showing the total figure to sum all quarters. Please help me to find out the way to resolve the issue.
Best regards,
Kaysar
Hi Kaysar,
The screenshot omits essential details for me to understand what you're doing and how your model is structured. Are you able to share the file with me, either here or sent to my email?
Mynda
Hi Mynda,
Thanks for your quick reply. I attached the screenshot again. if this not fulfill the requirement I will arrange to send the file. I just wanted to calculate the difference of Progress column value based on the Quarter column like Q4-Q3, or Q3-Q2 or Q2-Q1 in the specific year.
Best regards,
For the benefit of others reading this, Kaysar emailed me his Excel file containing the model. It turned out that the Date table was not marked as 'Date Table' and the PivotTable was not giving enough context for the measure to show the expected result i.e. it was at the all periods level, rather than the quarter level of detail.
I could never have even figured this out from some screenshots. Always send me your Excel file containing the data model if you can. You can make a copy of the file and remove sensitive information if required.
Mynda
This was a fantastic moment for me to work with Mynda and resolved the issue by her. My pleasure not only resolve it but also work with an expert and great mentor Mynda. Thanks Mynda for your close mentoring to resolve the real work issue. I learnt awesome tips beyond the course that wasn't so easy to resolve by me. Your courses are is awesome to learn Power Query/Power Pivot and Power BI and you are great supportive to resolve the issues related to BI
Best regards,
Kaysar
My pleasure, Kaysar 🙂
