cancel
Showing results for
Did you mean:
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))`

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

Kind regards,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar

## Re: Aggragating ignoring filters not working

Hi @mpatel ,

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

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.
3 REPLIES 3
Resident Rockstar

## Re: Aggragating ignoring filters not working

Hi @mpatel ,

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

## Re: Aggragating ignoring filters not working

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)

)```

Highlighted
Resident Rockstar

## Re: Aggragating ignoring filters not working

Hi @mpatel ,

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

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.

Announcements

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

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

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

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

Top Solution Authors
Top Kudoed Authors