Help need to write dax measure

[manojk_pbi]

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

[Anonymous · Accepted 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

[manojk_pbi]

@Anonymous , thanks for your quick response. I will check and try to understand your suggestion.