Monthly accruals

[Syndicate_Admin]

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 Cumulative Value

Export value,

Import value)

where "Export Value" and "Import Value" are two measures calculated from data from an "Input Data" table with a calendar "DateTime" Year, Month, Day and the accumulated are calculated as:

Export Cumulative Value = CALCULATE(Export Value,

ALLEXCEPT(DateTime, DateTime[DateTime].[ Year], DateTime[DateTime]. [Month],

FILTER(ALLSELECTED ('Input Data'[Fecha], 'Input Data'[Fecha]

(Same expression for import cumulation)

The monthly values are correct, but, when obtaining the annual values with the intelligence of times, it calculates the accumulated for the whole year, offering a value greater than the sum of the monthly values.

Can you help me obtain, by reducing in the calendar hierarchy to annual values, a value equal to the sum of the monthly values, not the one calculated with the annual accruals?

Thanks a lot

[amitchandak]

@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]

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