Aggragating ignoring filters not working

[Anonymous]

Hi,

I have created two measures :

• TotalBusinessDay for the current selected month (it works fine with ignoring filters) 

TotalBusinessDay = CALCULATE(SUM(Calendrier[WorkingDays]);ALL('Revenue'))

• BusinessdayLY for the last year total of business days corresponding the current selection 

BusinessDaysLY = CALCULATE( SUM(Calendrier[WorkingDays]);ALLEXCEPT(Revenue;Revenue[Date]) ; DATEADD(Calendrier[Date];-1;YEAR))

The behavior of the BusinessdayLY is wrong, I should have obtained the same result of TotalBusinessDay.

Could you please help me to resolve this please ?

Kind regards,

Mohammad

[v-piga-msft]

Hi @Anonymous ,

What about the measure below?

BusinessDaysLY =
CALCULATE (
    SUM ( Calendrier[WorkingDays] );
    ALLSELECTED ( Revenue );
    DATEADD ( Calendrier[Date]; -1; YEAR )
)

Is the slicer you created based on Calendar table or Revenue table?

If it is convenient, could you share some data sample and your desired output so that we could help further on it?

Best Regards,

Cherry

[Anonymous]

@v-piga-msft thank you for your answer.

The slicer I created is based on a Calendar table.

Here is the model :

Here's what i want to achieve :

N	TotalBusinessDay	TotalBusinessDaysLY
345000125	21	22
345000170	21	22
345000208	21	22
345000224	21	22
345000307	21	22
345000349	21	22
345000419	21	22
345000422	21	22
345000448	21	22
345000480	21	22
345000646	21	22
345000703	21	22
345000729	21	22
345000828	21	22
345000969	21	22
345001016	21	22
345001032	21	22
345001087	21	22
345001157	21	22

So whatever the number account I have I want to set the TotalBusinessDaysLY to be set and not change if I select another account number.

Here's a sample for my Revenue table :

N	Total_Revenue_LCY	Total_Shipments	Billed_Weight
345000125	0	1	0,5
345000170	109.80000305175781	1	2
345000208	77.87999725341797	2	1
345000224	324.0000057220459	5	17
345000307	277.34000396728516	5	3
345000349	65.62000274658203	1	0,5
345000419	92.12999725341797	1	0,5
345000448	253.93999481201172	4	3
345000480	678.7599945068359	3	53
345000703	63.86000061035156	1	0,5
345000729	69.19000244140625	1	0,5
345000828	70.63999938964844	1	0,5
345001032	131.5100040435791	2	1,5
345001467	498.63999938964844	13	6,5
345001470	149.2300033569336	2	3
345001665	115.77999877929688	2	1
345001889	1702.0099639892578	2	56,5
345002048	124.56999588012695	2	5
345002415	113.5999984741211	4	2
345002457	647.130012512207	3	20
345002501	18.43000030517578	0	0
345002527	69.16000366210938	1	0,5
345003070	70.05000305175781	1	0,5
345003083	199.89000701904297	3	1,5
345003153	1985.700050354004	89	254
345003179	153.0900001525879	3	1,5
345003265	199.2699966430664	3	5
345003715	1241.2899894714355	18	23,5
345003814	294.0499954223633	2	5,5

Here's the code for the Calendar table :

Calendrier = ADDCOLUMNS(
    CALENDAR (DATE(2018;1;1);EOMONTH(today();0))
   ;"Sorting";FORMAT([Date];"YYYYmmdd")   
   ;"Annee";YEAR([Date])                         
   ;"Mois #";MONTH([Date])                      
   ;"Mois Année";FORMAT([Date];"mmm YYYY")  
   ;"Mois Abrv.";FORMAT([Date];"mmm")       
   ;"Mois Nom";FORMAT([Date];"mmmm")           
   ;"Jour #";WEEKDAY([Date])           
   ;"Jour Nom";FORMAT([Date];"dddd")           
   ;"Jour Abrv.";FORMAT([Date];"ddd")      
   ;"Trimestre";"Q" & FORMAT([Date];"Q")          
   ;"Trimestre Année";FORMAT([Date];"YYYY") & " Q" & FORMAT([Date];"Q") 
;"EstAnneeCourant";IF(YEAR([Date]) = YEAR(NOW());1;0)
   ;"EstMoisCourant";IF(YEAR([Date]) = YEAR(NOW()) && MONTH([Date]) = MONTH(NOW());1;0)  
    ;"WorkingDays";IF(WEEKDAY([Date])=7 || WEEKDAY([Date])=1 || (DAY([Date])=1 && MONTH([Date])=1) || (DAY([Date])=1 && MONTH([Date])=5) 
 || (DAY([Date])=8 && MONTH([Date])=5) 
 || (DAY([Date])=14 && MONTH([Date])=7) 
 || (DAY([Date])=15 && MONTH([Date])=8)
 || (DAY([Date])=1 && MONTH([Date])=11)  
 || (DAY([Date])=11 && MONTH([Date])=11) 
 || (DAY([Date])=20 && MONTH([Date])=12) 
 || (DAY([Date])=25 && MONTH([Date])=12)
 || (FORMAT([Date];"dd/MM/yyyy")=FORMAT(ROUND (
     DATE ( Year([Date]);4;1 ) / 7
 + MOD ( 19 * MOD ( Year([Date]); 19 ) - 7; 30 )
 * 0,14;
     0
 )
 * 7
 - 5 ;"dd/MM/yyyy"))

 || (FORMAT([Date];"dd/MM/yyyy")=FORMAT(ROUND (
     DATE ( Year([Date]);4;1 ) / 7
 + MOD ( 19 * MOD ( Year([Date]); 19 ) - 7; 30 )
 * 0,14;
     0
 )
 * 7
 + 34 ;"dd/MM/yyyy"))

 || (FORMAT([Date];"dd/MM/yyyy")=FORMAT(ROUND (
     DATE ( Year([Date]);4;1 ) / 7
 + MOD ( 19 * MOD ( Year([Date]); 19 ) - 7; 30 )
 * 0,14;
     0
 )
 * 7
 + 44 ;"dd/MM/yyyy"))

|| (FORMAT([Date];"dd/MM/yyyy")=FORMAT(ROUND (
     DATE ( Year([Date]);4;1 ) / 7
 + MOD ( 19 * MOD ( Year([Date]); 19 ) - 7; 30 )
 * 0,14;
     0
 )
 * 7
 + 45 ;"dd/MM/yyyy"))


 ;0;1) 

)

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

Please try the measure below.

BusinessDaysLY_ =
VAR a =
    CALCULATE ( MAX ( 'Calendrier'[Annee] ) ) - 1
VAR b =
    MAX ( 'Calendrier'[Mois Abrv.] )
RETURN
    CALCULATE (
        SUM ( Calendrier[WorkingDays] ),
        FILTER (
            ALL ( 'Calendrier' ),
            Calendrier[Annee] = a
                && 'Calendrier'[Mois Abrv.] = b
        )
    )

Here is the output.

Best Regards,

Cherry