topic I in DAX Commands and Tips

I

Anonymous — Wed, 23 Sep 2020 23:53:00 GMT

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

calculate(sum(Data[Value]),filter(Data,Data[As Of]=max(Data[As Of])))

this works fine.

I am unable to figure out the measure for previous month. Max - 1 works, but that will obviously fail in January and when the data spans multiple years.

Re: I

amitchandak — Thu, 24 Sep 2020 02:48:11 GMT

Anonymous , 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.

Re: I

Anonymous — Fri, 25 Sep 2020 12:47:00 GMT

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

cq = CALCULATE(SUM(facts[For Period]),DATESQTD(ENDOFQUARTER(Dates[Date])))

I dont want to calculate / show cq for fact1 and fact2.