cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mpatel Regular Visitor
Regular Visitor

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

Capture d’écran 2019-03-26 à 07.19.57.png

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Aggragating ignoring filters not working

Hi @mpatel ,

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.

Capture.PNG

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Aggragating ignoring filters not working

Hi @mpatel ,

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mpatel Regular Visitor
Regular Visitor

Re: Aggragating ignoring filters not working

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

 

The slicer I created is based on a Calendar table.

 

Here is the model :

Capture d’écran 2019-03-26 à 07.23.03.png

 

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) 

)

 

Community Support Team
Community Support Team

Re: Aggragating ignoring filters not working

Hi @mpatel ,

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.

Capture.PNG

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors