Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sparramanga
Frequent Visitor

Dax Running Total working excepts for months with no values

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..

unfiltered.PNG

 

here is mimage with sector filter applied..

filtered.PNG


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

 

 



2 ACCEPTED SOLUTIONS
v-tangjie-msft
Community Support
Community Support

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

 

vtangjiemsft_0-1684742578176.png

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. 

View solution in original post

Sparramanga
Frequent Visitor

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

View solution in original post

4 REPLIES 4
Sparramanga
Frequent Visitor

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

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. 

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.

v-tangjie-msft
Community Support
Community Support

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

 

vtangjiemsft_0-1684742578176.png

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors