Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
iioannou
Helper I
Helper I

DAX help - Select value from column and row

Dear Community of Power BI,

 

 

I am having a simple question, I believe - I have the following DATA in a table

 

MonthDaysOccupancy
January31           4,209
February28           4,415
March31           4,523
April30           4,244
May31           4,345
June30           4,868
July31           5,020
August31           5,297
September30           5,161
October31           5,292
November30           5,189
December31           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])
)

 

 

1 ACCEPTED SOLUTION
sontt
Resolver I
Resolver I

You can use ealier function like that:

 

Budget = Calculate(sum(....), filter( table, month column = earlier(month column))

View solution in original post

5 REPLIES 5
onlyashish99
New Member

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. 

onlyashish99
New Member

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.

sontt
Resolver I
Resolver I

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.