cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ha_data
Frequent Visitor

SUM of PREVIOUS YEAR ignore filter/slicer selection

Hello,

 

I read a few similar threads but couldn't find one that would work with my measure. My measure DEVIS_N-1 which calculate sum of number of futures contract in the previous year doesn't react to the slicer "Etat de devis" like the mesure DEVIS_N (which calculate sum of number of futures contract in the year selected).

Below is my DAX formule for DEVIS_N and DEVIS_N-1:

DEVIS_N = IF(ISCROSSFILTERED(Affichage_dnb_devis[lib_affichage_devis]), 
SWITCH(TRUE(), 
        VALUES(Affichage_dnb_devis[lib_affichage_devis]) = "en Nombre", SUMX(sto_co, sto_co[cpt_DEVIS] * 1),
        VALUES(Affichage_dnb_devis[lib_affichage_devis]) = "en Montant prévisionnel", SUMX(sto_co, sto_co[cpt_DEVIS] * RELATED(Polices_devis[Mt_prévisionnel_Devis])),
        BLANK()), BLANK())
DEVIS_N-1 = CALCULATE('Mesure DEVIS'[DEVIS_N], ALLEXCEPT(sto_co,sto_co[periode]), SAMEPERIODLASTYEAR(Periode[fin]))

I have try to use this mesure below but it doesn't work :

Devis N-1 Test = VAR Selected_etat_devis = SELECTEDVALUE(Polices_devis[Etat du devis])
RETURN
CALCULATE('Mesure DEVIS'[DEVIS_N-1], FILTER(Polices_devis, Polices_devis[Etat du devis] = Selected_etat_devis))

I will send a simple version of my sample report to help you understand more about data model and its issue through my Onedrive for Business : https://eciliafr-my.sharepoint.com/:u:/g/personal/hnguyen_ecilia_fr/EfCF78y4LXRBknVZuc5zyJYBPuQrNkVw...

 

Thank you.

 

1 ACCEPTED SOLUTION
ha_data
Frequent Visitor

Finally I have found solution for this problem by using this mesure:

DEVIS_N-1 = CALCULATE('Mesure DEVIS'[DEVIS_N], DATEADD(Periode[fin], -1, YEAR), ALL(Periode))

 I have tried to create date table but it doesn't work. 

View solution in original post

3 REPLIES 3
ha_data
Frequent Visitor

Finally I have found solution for this problem by using this mesure:

DEVIS_N-1 = CALCULATE('Mesure DEVIS'[DEVIS_N], DATEADD(Periode[fin], -1, YEAR), ALL(Periode))

 I have tried to create date table but it doesn't work. 

View solution in original post

amitchandak
Super User
Super User

@ha_data , for time intelligence to work, you should a date table, marked as date table (all continuous dates). Also do not use this allexcept in this case

DEVIS_N-1 = CALCULATE('Mesure DEVIS'[DEVIS_N, SAMEPERIODLASTYEAR(Date[Date]))

 

 

Current table has only 1 date per month, that is not ideal for time intelligence

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

I need to use allexcept, if not the function doesn't work like the way you suggested. In addition, my date table is table "Periode", because I need to calculate the total by month so I coundn't use or create the date table like you suggested because it has no meaning in my case.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.