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 4 tables, 2 data tables and 2 tables for slicing. The data tables contain a week ID which is the Year+Weeknumber.
What i want to do is create a measure that sums the amount however if the WeekNum is 1 is should take the prior years week 53 and add it to the week 1 number of the current year.
So far i tried:
IF(MAX(WeekNumber[WeekNum])=52,blank(),IF(MAX(WeekNumber[WeekNum])=1,CALCULATE(SUM(DataTable1[Amount]),WeekNumber[WeekNum]=53,YrID[YearNum]-1)))
This however didnt work. It just returns the current years week 53 amount.
THe tables below are joined WeekNum to WeekNum and YearNum to YearNum
Data tables (which include WeekID 202053)
+--------+---------+---------+--------+
| WeekID | WeekNum | YearNum | Amount |
+--------+---------+---------+--------+
| 202001 | 1 | 2020 | 1234 |
| 202001 | 1 | 2020 | 2345 |
| 202104 | 4 | 2021 | 1234 |
+--------+---------+---------+--------+
This table is called WeekNumber which include weeknum 53
+---------+
| WeekNum |
+---------+
| 1 |
| 4 |
+---------+
This table is called YrID
+---------+
| YearNum |
+---------+
| 2020 |
| 2021 |
+---------+
Solved! Go to Solution.
@User7664 , You have to do this in date table as new column
Week = if( WEEKNUM([Date],2)=53,1, WEEKNUM([Date],2))
week year =year(Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2))
Hi @User7664 ,
According to your description, I create data from 2020.12.1 to 2021.2.8:
Here are the steps you can follow:
1. Create measure.
Measure =
IF(MAX('Date'[Weeknum])=1,
SUMX(FILTER(ALL('Date'),'Date'[YearNum]=MAX('Date'[YearNum])-1&&'Date'[Weeknum]=53),
'Date'[amount]),MAX('Date'[amount]))
2. Result.
The data of weeknum=1 in 2021 is 21, which is the sum of the amount of weeknum=53 in 2020
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@User7664 , You have to do this in date table as new column
Week = if( WEEKNUM([Date],2)=53,1, WEEKNUM([Date],2))
week year =year(Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2))
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |