Running sum showing all values

[Anonymous]

Hi guys,

currently I´m trying to create a line chart showing the running sum for all hours worked per month. This should look something like that:









I´m using following DAX to calculate the hours worked accumulated for every month:

Running sum hours worked =
VAR MaxDate = DATEVALUE([DatePicker]) // gets the date defined by the slicer selection
RETURN

CALCULATE([HoursWorked];FILTER(ALL(Mastercalendar);DATEVALUE(Mastercalendar[Date])<=MaxDate))

The [DatePicker]-Measure should work as it shows me the right selected date.

If I'm filtering the values by using the slicer, it always shows me the correct, accumulated sum for the selected month - Unfortunately just for the selected month and not for all available months until the selected date.

That's the result I'm currently receiving:



Thanks for your suggestions!

[v-lili6-msft]

hi  @Anonymous 

For your case, you need to use a separate calendar for slicer.

and here is a same logic case for you refer to:

https://community.powerbi.com/t5/Desktop/last-5-years-and-apply-year-filter-to-other-visuals/m-p/808765#M388901

Regards,

Lin

[az38]

Hi @Anonymous 

in fact you are trying to compare  DATEVALUE([DatePicker]) with DATEVALUE([DatePicker]) 🙂 of course, it gives you the only value. try

Running sum hours worked =
VAR MaxDate = DATEVALUE([DatePicker]) // gets the date defined by the slicer selection
RETURN

CALCULATE([HoursWorked];FILTER(ALL(Mastercalendar);SELECTEDVALUE(Mastercalendar[Date])<=MaxDate))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38,
thanks for your quick response!
If I´m changing my formula to what you´ve mentioned, it shows me the total accumulated sum for the last available month for the filtered month.
For example: I'm selecting 2019/12/31 it shows me the accumulated total for the last available date 2020/01/23 in the month of December 2019 - so not correct.

[az38]

@Anonymous 

it depends on your exactly data model

im not sure your statement 

MaxDate = DATEVALUE([DatePicker])

is correct. maybe it is better to use something like 

MaxDate = CALCULATE(MIN([DatePicker]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 ,

I tried that - then it logically shows no value.

My Mastercalendar looks like that:














That`s my formula for the [DatePicker]-Measure (fairly, that could probably be done much better...):

DatePicker = EOMONTH(DATE(if(ISFILTERED(Mastercalendar[Year]);SELECTEDVALUE(Mastercalendar[Year];"no filter");"no filter");if(ISFILTERED(Masterkalender[Monat_Name]);
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Januar";1;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Februar";2;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="März";3;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="April";4;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Mai";5;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Juni";6;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Juli";7;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="August";8;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="September";9;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Oktober";10;
    if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="November";11;
 if(SELECTEDVALUE(Masterkalender[Monat_Name];"no filter")="Dezember";12;"no filter")))))))))))));1);0)

Just to give you the full context, my slicers look like that:
















In my eyes it seems like the Mastercalendar isn't really giving all the dates below VAR MaxDate...

[az38]

@Anonymous 

and the second point: whats table do you add your initial measure [Running sum hours worked]?

let's say, you try to add it to 'Table1' try

Running sum hours worked = 
VAR MaxDate = DATEVALUE([DatePicker]) 
RETURN

CALCULATE([HoursWorked];FILTER(ALL('Table1');[Date]<=MaxDate))

because, your construction 

CALCULATE([HoursWorked];FILTER(ALL(Mastercalendar);SELECTEDVALUE(Mastercalendar[Date])<=MaxDate))

will try to caculate something somewhere in calendar table, but not in fact table

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@Anonymous 

in your statement I see two different calendar table names: Masterkalender and Mastercalendar

Is it correct?

do not hesitate to give a kudo to useful posts and mark solutions as solution