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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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