Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Aggragating ignoring filters not working

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

 

Need Help
Show and Tell
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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 :

       

      NTotalBusinessDayTotalBusinessDaysLY
      3450001252122
      3450001702122
      3450002082122
      3450002242122
      3450003072122
      3450003492122
      3450004192122
      3450004222122
      3450004482122
      3450004802122
      3450006462122
      3450007032122
      3450007292122
      3450008282122
      3450009692122
      3450010162122
      3450010322122
      3450010872122
      3450011572122

       

      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 :

       

      NTotal_Revenue_LCYTotal_ShipmentsBilled_Weight
      345000125010,5
      345000170109.8000030517578112
      34500020877.8799972534179721
      345000224324.0000057220459517
      345000307277.3400039672851653
      34500034965.6200027465820310,5
      34500041992.1299972534179710,5
      345000448253.9399948120117243
      345000480678.7599945068359353
      34500070363.8600006103515610,5
      34500072969.1900024414062510,5
      34500082870.6399993896484410,5
      345001032131.510004043579121,5
      345001467498.63999938964844136,5
      345001470149.230003356933623
      345001665115.7799987792968821
      3450018891702.0099639892578256,5
      345002048124.5699958801269525
      345002415113.599998474121142
      345002457647.130012512207320
      34500250118.4300003051757800
      34500252769.1600036621093810,5
      34500307070.0500030517578110,5
      345003083199.8900070190429731,5
      3450031531985.70005035400489254
      345003179153.090000152587931,5
      345003265199.269996643066435
      3450037151241.28998947143551823,5
      345003814294.049995422363325,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's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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