cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpatel Helper I
Helper I

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
v-piga-msft Resident Rockstar
Resident Rockstar

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
Highlighted
v-piga-msft Resident Rockstar
Resident Rockstar

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

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) 

)

 

v-piga-msft Resident Rockstar
Resident Rockstar

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors