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.
I have amounts. A start date and an end date.
I want to allocate the amounts into their weeks (red cells)
I can do this in excel using if statements. Can we do this in Power BI?
Thanks Team
Solved! Go to Solution.
Hi @Anonymous
Create two calculated tables
One is "date" table
date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],1))
Add a caluclated column in this table
weeknum = CALCULATE(MIN([Date]),ALLEXCEPT('date','date'[year],'date'[week]))
Another table
Table 2 = FILTER ( CROSSJOIN ( 'Table', FILTER ( 'date', 'date'[Date] <= CALCULATE ( MAX ( 'Table'[end date] ), ALL ( 'Table' ) ) && 'date'[Date] >= CALCULATE ( MIN ( 'Table'[start date] ), ALL ( 'Table' ) ) ) ), [Date] >= [start date] && [Date] <= [end date] )
Create a measure in this table
Measure = CALCULATE(SUM('Table 2'[amount per day]),ALLEXCEPT('Table 2','Table 2'[prepayment],'Table 2'[year],'Table 2'[weeknum]))
Hi @Anonymous
Create two calculated tables
One is "date" table
date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],1))
Add a caluclated column in this table
weeknum = CALCULATE(MIN([Date]),ALLEXCEPT('date','date'[year],'date'[week]))
Another table
Table 2 = FILTER ( CROSSJOIN ( 'Table', FILTER ( 'date', 'date'[Date] <= CALCULATE ( MAX ( 'Table'[end date] ), ALL ( 'Table' ) ) && 'date'[Date] >= CALCULATE ( MIN ( 'Table'[start date] ), ALL ( 'Table' ) ) ) ), [Date] >= [start date] && [Date] <= [end date] )
Create a measure in this table
Measure = CALCULATE(SUM('Table 2'[amount per day]),ALLEXCEPT('Table 2','Table 2'[prepayment],'Table 2'[year],'Table 2'[weeknum]))
Thanks Maggie! You are so smart, it works!
I've seen your responses on other things i have searched up and all have been so good. How did you get so smart?
But just a query, what is the purpose of the measure? Couldn't I still produce the same matrix without using measure but instead amount per day?
Hi @Anonymous
In Power BI, we need to create another table, it would be static, would you like this method?
Best Regards
Maggie
That would be good. Just as long as we can acheive the same output.
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |