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
pitucc
Helper I
Helper I

Date filtering on a cumatlive day on day variation measure

Hello

I have a measure that computes the Day on Day variation of some datas, this is a measure called DataBase[DoD DN]

I have another measure that computes the cumulative Day on day variation DataBase[DoD DN **bleep**] 

Everything works properly. I can plote in a line and stacked column charts and that's perfect. 

 

 Capture.PNG

Start Date 10jul

 

My probleme appires when I filter by dates, and move the start dates of what should be the start of the cumulative function? 

It seems that the date filter does not apply to the measure  so my cumumatlive does not starts at 0 

 

Capture.PNG

start dates 12aug 

 

 

Here his me DoD DN **bleep** measure 

 

DoD DN **bleep** = 
CALCULATE(SUMX('DataBase';'DataBase'[DoD DN]);
                   FILTER(
                           all('DataBase'[ValuationDate]);
                           'DataBase'[ValuationDate]<=MAX('DataBase'[ValuationDate])
                     )
 )

 

 

Any Advice ? 

Many thanks in advance for you help ! 

Pierre

 

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Employee
Employee

@pitucc,

You use ALL function in your cummulative measure, which ignores all date slicers and date selections in Visual/Page/Report filters.

Thus, when you apply date filter, the cummulative measure will show value of the first date(12aug) in your Axis, which is same as that value of the date(12aug) in the first screenshot. As long as the value is not 0, it will not be 0 in the second screenshot.


Regards,
Lydia

Community Support Team _ Lydia Zhang
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

indeed... I've changed the ALL by a ALLSELECTED and now it works

Thanks ! 

 

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@pitucc,

You use ALL function in your cummulative measure, which ignores all date slicers and date selections in Visual/Page/Report filters.

Thus, when you apply date filter, the cummulative measure will show value of the first date(12aug) in your Axis, which is same as that value of the date(12aug) in the first screenshot. As long as the value is not 0, it will not be 0 in the second screenshot.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

indeed... I've changed the ALL by a ALLSELECTED and now it works

Thanks ! 

 

pitucc
Helper I
Helper I

and here is the code of my DoD DN measure in case that's comming from here

DoD DN = 
VAR current_Product =LASTNONBLANK ( DataBase[Name]; [Name] )
VAR current_date = MAX ( DataBase[ValuationDate] )
VAR current_avg_parity =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate] = current_date
        );
        [Parity]
    )
VAR current_avg_cbprice =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate] = current_date
        );
        [Close]
    )

VAR previous_avg_parity =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate]
                    = MAXX (
                        FILTER (
                            ALL ( DataBase );
                            [Name] = current_Product
                                && [ValuationDate] <(current_date)
                        );
                        [ValuationDate]
                    )
        );
        [Parity]
    )
	VAR previous_avg_delta =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate]
                    = MAXX (
                        FILTER (
                            ALL ( DataBase );
                            [Name] = current_Product
                                && [ValuationDate] < (current_date)
                        );
                        [ValuationDate]
                    )
        );
        [Delta]
    )
	VAR previous_avg_cbpice =
    AVERAGEX (
        FILTER (
            ALL ( DataBase );
            [Name] = current_Product
                && [ValuationDate]
                    = MAXX (
                        FILTER (
                            ALL ( DataBase );
                            [Name] = current_Product
                                && [ValuationDate] < (current_date)
                        );
                        [ValuationDate]
                    )
        );
        [Close]
    )
	
	
RETURN
    IF ( and(previous_avg_parity <> 0;previous_avg_cbpice<>0); (current_avg_cbprice-previous_avg_cbpice)-previous_avg_delta*( current_avg_parity - previous_avg_parity); 0 )

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.