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

2 Replies

  • 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