Dax Running Total working excepts for months with no values

[Sparramanga]

Hi,

I have this measure that I have created that works fine with one caveat.. When using the measure on the y=axis and 'dim date'[month name] on the x-axis it display correctly, however..
When a filter is applied to the 'supported business' table for sector this reduces the running total amount correctly but for any months where the chosen sector value doesnt have a value the the x axis doesnt show that month, skips it and continues to the next month..

here is image with no filter applied..

here is mimage with sector filter applied..

and here is my measure..

A SB Cumulative Deal Amount Previous FY =

Var maxdate =

calculate(

        max('Supported Business'[RR If Cp Else GI Date])

        ,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31)

)

Var Result =

calculate(

            sum('Supported Business'[RR Liability GBP])

            , allselected('Supported Business')

            ,'Supported Business'[RR If Cp Else GI Date] <=maxdate

            ,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31)

            )

return

Result

This measure gets the total for the previous financial year so the inital var MaxDate is getting the the last day of sales for that financial year.

I am using the date column [RR If Cp Else GI Date]  and getting anything <=maxdate to get the running total.

I have tried to use the 'dim date'[date] column as always suggested in any examples ive rad on help forums but could never get that to work.

Is there something i can apply to my current code withou changing the measure to use 'dim date'[date] so that we dont skip a month on the x axis if no sales but just replicate the previous running total value?

Thanks,

R

[Anonymous · Accepted solution]

Hi @Sparramanga ,

Please try this measure.

A SB Cumulative Deal Amount Previous FY = 

Var maxdate =
calculate(
        max('Supported Business'[RR If Cp Else GI Date])
        ,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31),REMOVEFILTERS('Supported Business'[Sector])
)


Var Result =
calculate(
            sum('Supported Business'[RR Liability GBP])
            
            , allselected('Supported Business')
             
            ,'Supported Business'[RR If Cp Else GI Date] <=maxdate
            ,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31)
            )

return
Result

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Sparramanga · Accepted solution]

Brilliant thanks! That seems to do exactly what i wanted on first look 🙂

What is the reasoning behind removing filters in the maxdate variable?

I was trying several context filters in the calculate expression but to no avail.

And was even thinking about creating a dummy table with a value of zero for at least one date of each month and adding this total to the existing total to make sure all dates had an entry against them.. but thought there must be a simple solution.

Thanks,

R

[Anonymous]

Hi @Sparramanga ,

REMOVEFILTERS() can clear filters from the specified tables or columns. 

You can refer to the following documents that may be helpful to you:

REMOVEFILTERS function (DAX) - DAX | Microsoft Learn

REMOVEFILTERS() in DAX - Excelerator BI

Best Regards,

Neeko Tang

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

[Sparramanga]

Yeah i will check the documentation again.. i think i was unsure as to why removefilters on the sector column would still let me filter the runing total amount by sector, so it reduces the total correctly but still keep the months on the x axis in tact.

[Anonymous · Accepted solution]

Hi @Sparramanga ,

Please try this measure.

A SB Cumulative Deal Amount Previous FY = 

Var maxdate =
calculate(
        max('Supported Business'[RR If Cp Else GI Date])
        ,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31),REMOVEFILTERS('Supported Business'[Sector])
)


Var Result =
calculate(
            sum('Supported Business'[RR Liability GBP])
            
            , allselected('Supported Business')
             
            ,'Supported Business'[RR If Cp Else GI Date] <=maxdate
            ,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31)
            )

return
Result

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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