Forum Discussion

GKs's avatar
GKs
Frequent Visitor
6 years ago
Solved

Running Total Specific Year

I would like to be able to compare a specific year against another by means of its days, that is to say that in a month X from day 1 to 30 to be able to see the evolution of sales day by day in an accumulated way.

The table below is where I was able to get through the formulas described.

Cump Ac 2019 = CALCULATE([2019 Sell Out]), FILTER(ALLSELECTED(Table[Sell Out]),Table[Date] <= MAX(Table[Date])))
Cump Ac 2018 = CALCULATE([2018 Sell Out], FILTER(ALLSELECTED(Table[Sell Out]),Table[Date] <= MAX(Table[Date] )))
For 2019 it is working as expected, however for the 2018 part it continues to bring the values even when it no longer corresponds to 2018.

 

 

Data Protection
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Try this measure

    Cump Ac 2018 = IF(ISBLANK([2019 Sell Out]),BLANK(),CALCULATE([2018 Sell Out], FILTER(ALLSELECTED(Table[Sell Out]),Table[Date] <= MAX(Table[Date] ))))

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Try this measure

    Cump Ac 2018 = IF(ISBLANK([2019 Sell Out]),BLANK(),CALCULATE([2018 Sell Out], FILTER(ALLSELECTED(Table[Sell Out]),Table[Date] <= MAX(Table[Date] ))))

    • GKs's avatar
      GKs
      Frequent Visitor
      6 years ago

      thank you very much for your help.
      I think the problem may be in the blank is only taking the range within 2019.

      Try this way however the same happens if I include the If with the ISBLANK and BLANK.

       

      Cump Ac % 2018 =
      VAR AC2018= CALCULATE(SUM(Table Sell out[Sell Out]), FILTER(ALLSELECTED(Table date),Table date[date] <= MAX(Table date[date])))
      RETURN
      CALCULATE( AC 2018,
      SAMEPERIODLASTYEAR(Table date[date]),ALL(Table date))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI GKs ,

        These blank parts mean they not have corresponded table records, I think you need a calendar table with whole dates. Then you can use calendar date as axis and looping table records based on the calendar date.

        Understanding DAX Auto-Exist 

        Cumulative Total 

        Regards,

        Xiaoxni Sheng