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
FRG
Resolver I
Resolver I

Sum last value filtered by date

Hello,

I need to get the last CUMULATIF value for each single NO_PALETTE filterd by the choosen date. The date slicer is coming from a calendar table. Then I want to sum all the value the get the total. I tried this measure but it don't give me the total:

 

 

Qty(filter)_test = 
var dateselect = SELECTEDVALUE(Dimdate[Date])
var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
Return
CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)

 

 

 

For September 22nd 2021 it should be 612.

FRG_0-1655221290708.png

 

For Septembre 23td 2021 it should be 1052.

FRG_1-1655221359990.png

Thank you!
Here is the PBIX file:  https://www.dropbox.com/s/5eg1o2nng8loitp/LastValue.pbix?dl=0 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@FRG Your measures needs to sum up the across all distinct values of NO_PALETTE, like this:

 

Qty(filter)_test = 
    SUMX(
        VALUES(HIST_PAL[NO_PALETTE]),
        var dateselect = SELECTEDVALUE(Dimdate[Date])
        var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
        Return
        CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)
    )

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

Tu peux corriger ta mesure comme ça,

 

=
VAR dateselect = SELECTEDVALUE( Dimdate[Date] )
RETURN
    SUMX(
        VALUES( HIST_PAL[NO_PALETTE] ),
        VAR minindex =
            CALCULATE(
                MIN( HIST_PAL[Index] ),
                HIST_PAL[DATE] <= dateselect,
                ALLEXCEPT( HIST_PAL, HIST_PAL[NO_PALETTE] )
            )
        RETURN
            CALCULATE( SUM( HIST_PAL[CUMULATIF] ), HIST_PAL[Index] = minindex )
    )

 

mais elle marche pas assez efficacement; voici une mesure 30+ fois plus vite (selon DAXStudio, 15ms vs 500ms) bien qu'elle ait l'air bcp plus compliquée.

 

=
VAR dateselect = SELECTEDVALUE( Dimdate[Date] )
RETURN
    SUMX(
        VALUES( HIST_PAL[NO_PALETTE] ),
        CALCULATE(
            SUM( HIST_PAL[CUMULATIF] ),
            KEEPFILTERS(
                TOPN(
                    1,
                    CALCULATETABLE(
                        HIST_PAL,
                        HIST_PAL[DATE] <= dateselect,
                        ALLEXCEPT( HIST_PAL, HIST_PAL[NO_PALETTE] )
                    ),
                    HIST_PAL[Index], ASC
                )
            )
        )
    )

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

@FRG Your measures needs to sum up the across all distinct values of NO_PALETTE, like this:

 

Qty(filter)_test = 
    SUMX(
        VALUES(HIST_PAL[NO_PALETTE]),
        var dateselect = SELECTEDVALUE(Dimdate[Date])
        var minindex = CALCULATE(MIN(HIST_PAL[Index]),HIST_PAL[DATE] <= dateselect,ALLEXCEPT(HIST_PAL,HIST_PAL[NO_PALETTE]))
        Return
        CALCULATE(SUM(HIST_PAL[CUMULATIF]),HIST_PAL[Index] = minindex)
    )

Thank you @Anonymous and @CNENFRNL . Your solutions works!

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.