Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to Solution.
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
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
@v-yangliu-msft , thanks for your quick response. I will check and try to understand your suggestion.
User | Count |
---|---|
15 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
19 | |
4 | |
3 | |
1 | |
1 |