Forum Discussion

shubh25's avatar
shubh25
Helper I
7 years ago
Solved

Cumulative sum by dates (Even the missing dates)

Hi, I am using measure to calculate profit across the dates. This is the data that I am using. Date, Revenue on that date and Cumulative revenue. Cumulative value even when I choose a part...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi shubh25 ,

     

    One sample for your reference, please check the following steps as below.

    1. Create a calendar table as below and make it related to the fact table.

    CALENDAR = CALENDAR(DATE(2019,01,01),DATE(2019,01,31))

     

    2. To create a measure.

    Measure = CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('CALENDAR'),'CALENDAR'[Date]<=MAX('CALENDAR'[Date])))

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

I would use GENERATESERIES to create a new table with all of the dates you need, or just CALENDAR or CALENDARAUTO perhaps. Then you could use that as your axis.

shubh25's avatar
shubh25
Helper I
7 years ago

Generating all dates for all the data set will create millions of rows which will slow down the pbix significantly.