cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AccrualJoke
Helper I
Helper I

Cumulative (Running) Totals for Specified Period Based on Single Date Selection

I'm struggling trying to find a measure that can do the following:

  • Based on a date selection from a single-select date slicer, calculate the cumulative (running) totals for the last 5 days.
  • Relative date slicer CANNOT be used to achieve this

For example, assuming the data begins January 1, 2020. If Nov 30, 2020 is selected from the date slicer, the corrresponding visual (bar chart with dates as x-axis) should show Nov 25, 26, 27, 28, 29, 30 along the x-axis while the counts for each date should equal to the amounts from Jan 1, 2020 to Nov 25, Jan 1 to Nov 26...


Unforunately, every combination I try results in:

  • Counts flat lining from inception to date selected
  • Counts calculating correctly, but dates stuck at inception to date selected
  • Counts showing for ONLY the selected date

If someone could help provide a solution, i'd be extremely greatful. Thank you!

1 ACCEPTED SOLUTION
DataZoe
Microsoft
Microsoft

Hi @AccrualJoke ,

 

You can do this by creating a disconnected date table for the picker (this means it's not connected by relationship to another table in the model). You can do this with the TABLE DAX:

 

[Date Picker] = SUMMARIZE(MOCK_DATA,MOCK_DATA[date])

Then the measure for your chart:
[Measure] =
var pickeddate = SELECTEDVALUE('date picker'[date])
var ondate = SELECTEDVALUE(MOCK_DATA[date])
return
if(SELECTEDVALUE(MOCK_DATA[date])<=pickeddate && SELECTEDVALUE(MOCK_DATA[date])>=pickeddate-5,
TOTALYTD(sum(MOCK_DATA[value]),MOCK_DATA[date]))


Then you would NOT use the date picker date on your chart, the other one.

 

DataZoe_1-1606159050070.png

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

7 REPLIES 7
DataZoe
Microsoft
Microsoft

Hi @AccrualJoke ,

 

You can do this by creating a disconnected date table for the picker (this means it's not connected by relationship to another table in the model). You can do this with the TABLE DAX:

 

[Date Picker] = SUMMARIZE(MOCK_DATA,MOCK_DATA[date])

Then the measure for your chart:
[Measure] =
var pickeddate = SELECTEDVALUE('date picker'[date])
var ondate = SELECTEDVALUE(MOCK_DATA[date])
return
if(SELECTEDVALUE(MOCK_DATA[date])<=pickeddate && SELECTEDVALUE(MOCK_DATA[date])>=pickeddate-5,
TOTALYTD(sum(MOCK_DATA[value]),MOCK_DATA[date]))


Then you would NOT use the date picker date on your chart, the other one.

 

DataZoe_1-1606159050070.png

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

@DataZoe Thank you so much! I just tried your measure now and it's working well!

The one issue is that I'll need to calculate totals beyond just the year. The data will most likely spanning past 2020 into future periods. So is there a variation that allows for that?

 

I tried substituting TOTALYTD with Calculate, but that doesn't seem to work.

Please let me know if you have further insight to this! Thank you again!

@AccrualJoke ,

 

The TOTALYTD will work on any period, so should accomodate future years just like it is.

DataZoe_0-1606164100895.png

 

 

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Is there a way to make it so that the cumulative count does NOT reset at the beginning of a new year?

For example, instead of a TOTALYTD is there an equivalent for life-to-date or inception-to-date? I would want 2021 to include the 2020 and prior year counts as part of the 2021 totals.

@AccrualJoke Sure, you can use this cumulative instead:

 

Cumulative =
CALCULATE (
    [Measure],
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        ISONORAFTER ( 'Date'[Date], MAX ( 'Date'[Date] )DESC )
    )
)

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Thanks again for saving me! 

Instead of referencing the Date column from my disconnected Date Table, I used the date column from my actual data table. After making the switch, the measure seemed to work perfectly fine.

EDIT: 2022-10-03 -- A year later and I'm still finding myself using this as reference! For clarity, I've included the full measure I ended up using. For the running total measure to work as intended, it needed to include the condition to return a value of BLANK if the date from the fact table is greater than the selected date from the disconnected date table.

 

RUNNING / CUMULATIVE TOTAL =

IF (
    MAX ( Fact_Table[Date] ) > SELECTEDVALUE ( Disconnected_Date_Table[Date] ),
    BLANK (),
    CALCULATE (
        SUM ( Fact_Table[Sales] ),
        FILTER (
            ALLSELECTED ( Fact_Table[Date] ),
            Fact_Table[Date] <= MAX ( Fact_Table[Date] )
        )
    )
)

Alternatively, one could use ISONORAFTER

RUNNING / CUMULATIVE TOTAL V2 =

IF (
    MAX ( Fact_Table[Date] ) > SELECTEDVALUE ( Disconnected_Date_Table[Date] ),
    BLANK (),
    CALCULATE (
        SUM ( Fact_Table[Sales] ),
        FILTER (
            ALLSELECTED ( Fact_Table[Date] ),
            ISONORAFTER ( Fact_Table[Date], MAX ( Fact_Table[Date] ), DESC )
        )
    )
)

 

 

 

@AccrualJoke ah perfect, I am glad you got it to work 🙂 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.