Running total

[Anonymous]

Hello all,

I've this running total measure:

Running Total Ferien Rest =

CALCULATE(

SUM(DIA_SAP_ABSENZEN[Ferien Rest]),

FILTER(

ALLSELECTED(DIA_SAP_ABSENZEN),

DIA_SAP_ABSENZEN[Datum] <= MAX(DIA_SAP_ABSENZEN[Datum])

)

)

It gives me the correct running total up to a specific date, or within a specific date range, which is good.

However, now I need it to give me the running total for up to any given date, or a date range, which will also include all data since inception. For example, if I choose 2020, then I'd like the remaining balance from 2019/18/17 etc to be icluded, whilst at the moment it only gives me the running total for 2020.

Is this doable with this measure, or should I use something else?

[Anonymous]

Hi @AlB 

V4 did the trick. Thanks very much!

I've another complicated question. I'll run it by you on a private message before posting it in the open.

[AlB · Accepted solution]

@Anonymous 

I see that you're using the Date table fields for slicing. That's much better. Try this:

Measure V4 = 
VAR max_ =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( SUM ( Table1[Ferien Rest] ), 'Date'[Date] <= max_, ALL ( 'Date' ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB 

Thank you for the new measure!

It works as far as filtering by colleague-name is concerned:

However, the moment I start filtering using the dates slicers.

The year filter for 2020 brings 0, when it should be 50.30:

And, when I add Q4 to the filter, it's -58.80, when it should be 50.30:

Can you please help with fixing the measure?

I've now updated the .pbix file on the Drive: https://drive.google.com/file/d/1I0mTOcldE0oLSKKFI_iyvYgUMKjPmtKG/view?usp=sharing

I look forward to hearing back from you.

Cheers.

[AlB]

@Anonymous 

MeasureV3 =
CALCULATE (
    SUM ( Table1[Ferien Rest] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Datum] <= MAX ( Table1[Datum] ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB 

Thanks for your answer.

Unfortunately, its still doesn't work. It gives the wrong total, the moment I filter on the colleague-name, even without applying any date filters from the slicers (the running balance should be 50.3):

Any other suggestions please?

[AlB]

@Anonymous 

Perhaps I have misunderstood but this is very simple:

Measure V2 =
CALCULATE (
    SUM ( Table1[Ferien Soll] ),
    FILTER ( ALL ( Table1 ), Table1[Datum] <= MAX ( Table1[Datum] ) )
)

This will give you the cumulative value from the beginning of time up to the max date selected with the slicer. Do note that setting the slicers up like you have, quarter, month, week, etc all separately, you can have any kind of combination in the slicers. It is recommened to use a date table for the slicers instead of the date columns in the fact table. You have the date table already in the model; use it      

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB and @amitchandak 

You can find the file here: https://drive.google.com/file/d/1I0mTOcldE0oLSKKFI_iyvYgUMKjPmtKG/view?usp=sharing

Looking forward to your feedback.

Many thanks.

[Anonymous]

Hi @AlB and @amitchandak 

How can I share/attach the .pbix file? I tried to attach it to this message, but it say it is not supported.

Thanks.

[AlB]

@Anonymous 

It will be difficult to fix without the pbix. Can you share it? Or a version with mock data that repoduces the issue?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @amitchandak 

Thanks for your response.

Unfortunately it didn't work. 

- When I don't use any slicer filters at all, then it gives me a number slightly lower than what it should be, and it doesn't seem to correspond to any totals I can glean from my data:

- When I apply a filter for 2020, then I get a smaller figure than what it should be, and it still doesn't correspond to my totals:

- When I filter on a colleague name, then I get a huge number, unrelated to anything I have:

Could you please look into it?

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

Running Total Ferien Rest =

var _max = maxx(allselected(DIA_SAP_ABSENZEN),DIA_SAP_ABSENZEN[Datum])

return
CALCULATE(
SUM(DIA_SAP_ABSENZEN[Ferien Rest]),
FILTER(
ALL(DIA_SAP_ABSENZEN),
DIA_SAP_ABSENZEN[Datum] <= MAX(DIA_SAP_ABSENZEN[Datum])

&& DIA_SAP_ABSENZEN[Datum] < _max
)
)