Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have monthly data on a set of KPIs, and want to create a table that gives the value of each KPI for the latest period as well as the value in the previous period (last month).
KPI AsOF Value
A 8/31 99
A 7/31 88
B 8/31 2.5
B 7/31 2.9
I want the output table to look like this
KPI Current MOnth Prev Month
A 99 88
B 2.5 2.9
I wrote a measure for Current Month
@pradiptasaha , Create a date table join it with AsOF
Try measures like
MTD = CALCULATE(SUM(Table[Value]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(SUM(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE(SUM(Table[Value]),previousmonth('Date'[Date]))
this month = CALCULATE(SUM(Table[Value]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) = CALCULATE(SUM(Table[Value]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))
diff = [MTD]-[last MTD]
diff % = divide([MTD]-[last MTD],[last MTD])
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
Appreciate your Kudos.
Thanks so much. This didnt exactly solve my problem, but much more importantly led me to a better understanding of date dimensions in DAX and how to think about the data model itself. I have made the monthly measures work with some reworking of the data tables, with one remaining challenge that is coming from the fact that some of my facts are captured monthly, some quarterly, and when I use a QTD, the months get summed for the monthly facts as well. Is there a way to suppress the calculation for the metrics that are monthly?
I am using