Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

Monthly accruals

I have a measure that compares the monthly cumulative value of two other measures and offers the value of the one you accumulate less in the month: Compensation value = IF(Export Cu...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Syndicate_Admin , Not clear, In case you need MTD, with help from date table and time intellignece

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

YTD

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

 

Need Cumulative

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

 

You can also consider window

 

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

Hello, excuse me. I cannot solve the problem with either DATESMTD or WINDOW. I calculate the monthly accruals correctly, and, comparing the two accumulated, I calculate the monthly value of the "Compensation" measure. My problem is that I cannot add the monthly compensation values in the annual calculation. Instead of obtaining the sum of the 12 monthly values obtained from the monthly accumulated I obtain a new value of "Compensation", calculated from a comparison of those accumulated in the annual calculation.

If the origin were columns, I would create a column calculated with the "Compensation" values and could easily add the monthly values, but the origin is measured.

I copy below the results I get. You will see that the sum of the 12 values of "Compensation" of 2022 does not add up to 489,707, but should appear close to 395,000.

The measure "Compensation" is defined as Compensation = IF([Cumulative exports] < [Cumulated imports], [Exports], [Imports])

Thank you very much again