How to calculate cumulative total, if date falls between date range?

[Anonymous]

Hi,

I'm currently working on something where i have to calculate cumulative totals, where i have to check if a date from my calendar table falls in a certain date range of an other table. And if that is the case i have to calculte for each day how many id's there are per version. So far i haven't find the correct DAX formula to calulate it. I have put some dummy data down below

table IDVersion

ID                  Version        StartDate            EndDate

1                        7.0           2003-07-02        2004-05-05

2                        8.0           2013-10-09        2016-03-03   

3                        7.1           2005-06-07        2008-09-07

4                        7.0           2004-03-13        2005-10-21

5                        8.0           2014-12-24        2015-07-11

...                        ...                     ...                        ...

tabel Calendar

ID                        Date

1                     2003-01-01

2                     2003-01-02

3                     2003-01-03

4                     2003-01-04

5                     2003-01-05

...                            ...

Result (Table Visual in Power BI)

Date                        Version                    CumulativeIDs (measure)

2003-07-02                7.0                                           1

2003-07-03                7.0                                           1

2003-07-04                7.0                                           1

        .                            .                                             .

        .                            .                                             .

        .                            .                                             .

2004-03-13                7.0                                           2

2004-03-14                7.0                                           2

[v-xicai · Accepted solution]

 Hi @Anonymous 

Firstly, create calendar table named Calendar, and create measure CumulativeIDs.

Calendar = CALENDAR("2003,01,01",TODAY())

CumulativeIDs =

var d=SELECTEDVALUE('Calendar'[Date])

var v=SELECTEDVALUE(IDVersion[Version])

return COUNTROWS(FILTER(IDVersion,IDVersion[Version]=v&&d<=IDVersion[EndDate]&&d>=IDVersion[StartDate]))

Then, choose table visual to display the result.

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUQiegXq7-ZNhJtpjT0MqSgByeUbs5njxIYU8h3-ME6wFw?e=8S5PWu

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai,

Your solution helped me a lot, i can now you use this measure to make another one that allows me to visualize this in a line chart. Thanks for your help!

Best regards,

@Anonymous