Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have created two measures :
TotalBusinessDay = CALCULATE(SUM(Calendrier[WorkingDays]);ALL('Revenue'))
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
Solved! Go to 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
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
@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) )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |