Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Community of Power BI,
I am having a simple question, I believe - I have the following DATA in a table
Month | Days | Occupancy |
January | 31 | 4,209 |
February | 28 | 4,415 |
March | 31 | 4,523 |
April | 30 | 4,244 |
May | 31 | 4,345 |
June | 30 | 4,868 |
July | 31 | 5,020 |
August | 31 | 5,297 |
September | 30 | 5,161 |
October | 31 | 5,292 |
November | 30 | 5,189 |
December | 31 | 3,112 |
And I have another table and I want to create a new column which should basically pick the values from the table above...
So basically - my DAX should be changed so that it picks the value from the Occupancy column for each of the month accordingly
Budget = SWITCH('Table Rooms'[StatisticDateMonth],"January", SUM('Table Rooms Budget'[January]), "February", SUM('Table Rooms Budget'[February]), "March", SUM('Table Rooms Budget'[March]), "April", SUM('Table Rooms Budget'[April]), "May", SUM('Table Rooms Budget'[May]), "June", SUM('Table Rooms Budget'[June]), "July", SUM('Table Rooms Budget'[July]), "August", SUM('Table Rooms Budget'[August]), "September", SUM('Table Rooms Budget'[September]), "October", SUM('Table Rooms Budget'[October]), "November", SUM('Table Rooms Budget'[November]), "December", SUM('Table Rooms Budget'[December])
)
Solved! Go to Solution.
You can use ealier function like that:
Budget = Calculate(sum(....), filter( table, month column = earlier(month column))
You need to add a column into table 1 from table 2 with reference to month name. right?
You can use Merge in edit queries to add column from table 1 to table 2. Select month name as common key between table 1 and table 2 an d then expand the required column.
To add a new column from another table you can user merge (edit queries) instead of writing DAX. Merge Occupancy and budget table with month name common key. Then expand the required column into table 1.
You can use ealier function like that:
Budget = Calculate(sum(....), filter( table, month column = earlier(month column))
How can I apply this to my query ?
Please give me sample data, then i will try give u calculate function.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |