Forum Discussion
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 particular filter comes correct as per the overall amount.
e.g.,2018 data.
But I would like to plot this on a clustered column chart so I need cumulative sum on all the days.
I use :
I am getting these missing dates in 3rd picture because the overall table does not contain those dates.
Is there any measure or formulae that I can use to get the missing dates.
Please help.
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])))
5 Replies
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.
Generating all dates for all the data set will create millions of rows which will slow down the pbix significantly.
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])))I am trying to do that already. However, rest of the filters are in another table i.e., "Finacials". Is there a way that I can use all and selectedvalue on columns from different tables.
i.e., DATES from CALENDER & SOURCENAME from FINANCIALS.
The DAX expression I am currently using-Profit over period =CALCULATE ([Revenue],FILTER (ALL ( Financials ),Financials[TxnDate] <= MAX ( Financials[TxnDate] )&& Financials[Source.Name] = SELECTEDVALUE ( Financials[Source.Name] )))
Hi,
What is the difference between the 3rd and 4th Table?
