Forum Discussion
Help need to write dax measure
Hi,
I have 2 tables, one with tasks details by project and another is cost% by projects. I need to create a visualization to show the Net change in the tasks Month on Month by Project and another table to show Netchange with respect to cost share (NetChange * Cost%).
Appreciate any suggestions or sample code for DAX
Data tables
| Table 1 | ||
| Project | Date | Tasks |
| Project1 | Jan'24 | 120 |
| Project2 | Jan'24 | 100 |
| Project3 | Jan'24 | 130 |
| Project1 | Feb'24 | 110 |
| Project2 | Feb'24 | 90 |
| Project3 | Feb'24 | 100 |
| Project1 | Mar'24 | 80 |
| Project2 | Mar'24 | 80 |
| Project3 | Mar'24 | 145 |
| Project1 | Apr'24 | 95 |
| Project2 | Apr'24 | 75 |
| Project3 | Apr'24 | 125 |
| Table 2 | ||
| Project | Cost% | |
| Project1 | 26% | |
| Project2 | 35% | |
| Project3 | 39% |
required output:
| Table1 for display | ||||
| Project | Jan'24 | Feb'24 | Mar'24 | Apr'24 |
| Project1 | 120 | 110 | 80 | 95 |
| Project2 | 100 | 90 | 80 | 75 |
| Project3 | 130 | 100 | 145 | 125 |
Net Change : Baseline month - current month eg Jan'24-Feb'24 , Jan'24 - Mar'24, Jan'24 - Apr'24
| Project | Jan'24 | Feb'24 | Mar'24 | Apr'24 |
| Project1 | 10 | 40 | 25 | |
| Project2 | 10 | 20 | 25 | |
| Project3 | 30 | -15 | 5 |
Net Change * Cost% : for Project1 Feb'24 = 10*26%
| Project | Jan'24 | Feb'24 | Mar'24 | Apr'24 |
| Project1 | 3 | 10 | 7 | |
| Project2 | 4 | 7 | 9 | |
| Project3 | 12 | -6 | 2 |
Hi manojk_pbi ,
Because Power BI sorts by default based on alphabetical order, we need to create a sort table
Here are the steps you can follow：
1. Create calculated table
Date = var _date= CALENDAR( DATE(2024,1,1), DATE(2024,12,31)) return ADDCOLUMNS( _date,"MonthYear",FORMAT([Date],"mmm")&"'"&FORMAT([Date],"yy"))Sort_Table = SUMMARIZE('Date','Date'[MonthYear],"mindate",MINX(FILTER(ALL('Date'),'Date'[MonthYear]=EARLIER('Date'[MonthYear])),[Date]))2. Select [MonthYear] – Column tools – Sort by column – [mindate].
3. Joining two tables.
4. Create measure.
Baseline month - current month = var _today=TODAY() var _monthyear=MINX(FILTER(ALL('Date'),'Date'[Date]=DATE(YEAR(_today),1,1)),[MonthYear]) var _value=SUMX(FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Date]=_monthyear),[Tasks]) RETURN _value - MAX('Table'[Tasks])Net Change * Cost% = var _value= SUMX(FILTER(ALL('Table2'), 'Table2'[Project]=MAX('Table'[Project])),[Cost%]) return _value * [Baseline month - current month]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
2 Replies
Hi manojk_pbi ,
Because Power BI sorts by default based on alphabetical order, we need to create a sort table
Here are the steps you can follow：
1. Create calculated table
Date = var _date= CALENDAR( DATE(2024,1,1), DATE(2024,12,31)) return ADDCOLUMNS( _date,"MonthYear",FORMAT([Date],"mmm")&"'"&FORMAT([Date],"yy"))Sort_Table = SUMMARIZE('Date','Date'[MonthYear],"mindate",MINX(FILTER(ALL('Date'),'Date'[MonthYear]=EARLIER('Date'[MonthYear])),[Date]))2. Select [MonthYear] – Column tools – Sort by column – [mindate].
3. Joining two tables.
4. Create measure.
Baseline month - current month = var _today=TODAY() var _monthyear=MINX(FILTER(ALL('Date'),'Date'[Date]=DATE(YEAR(_today),1,1)),[MonthYear]) var _value=SUMX(FILTER(ALL('Table'),'Table'[Project]=MAX('Table'[Project])&&'Table'[Date]=_monthyear),[Tasks]) RETURN _value - MAX('Table'[Tasks])Net Change * Cost% = var _value= SUMX(FILTER(ALL('Table2'), 'Table2'[Project]=MAX('Table'[Project])),[Cost%]) return _value * [Baseline month - current month]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
Anonymous , thanks for your quick response. I will check and try to understand your suggestion.
