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

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

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

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.

I am in same boat, Did you get any solution?
I have column of Fiscal Year and Fiscal week. I wanto get lats year this week data based on ficsal week selected in slicer.

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.