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.
here last three weeks data and month wise data is combined ,I want to calculate change and change percentage .
last week data must be subtracted with last month of data and their percentage.
Can you help me guys with this .
Solved! Go to Solution.
Hi @N_Kotiyal ,
Please try this DAX:
CALCULATED_TABLE =
VAR CurrentWeek =
CALCULATE(
MAX('Sample'[Week]),
'Sample'[Date] = TODAY()
)
RETURN
SUMMARIZE(
'Sample',
'Sample'[Player Name],
"LastMonthValue",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"LastWeekValue",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"Change",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
) -
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"Change %",
DIVIDE((CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
) -
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
)),
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
)
))
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @N_Kotiyal ,
@_AAndrade Thanks for your concern about this case!
@N_Kotiyal If your dataset is just looked as this:
You can use these DAXs to create two calculated columns:
Change = [21/04/2024] - [31/03/2024]
Change % = DIVIDE([Change], [31/03/2024])
And when you create visuals, put these four columns together:
If your dataset doesn't look like the above, then I can give you an example.
Here is my sample data:
If you want to calculated the Value last month, you can use this DAX to create a measure:
Last Month =
VAR _today = TODAY()
VAR _lastmonth = MONTH(TODAY()) - 1
RETURN
CALCULATE(
SUM('Sample'[Value]),
MONTH('Sample'[Date]) = _lastmonth
)
If you want to calculated the Value last week, you can use this DAX to create a measure:
Last Week =
VAR _Todayweek =
CALCULATE(
MAX('Sample'[Week]),
'Sample'[Date] = TODAY()
)
VAR _Lastweek = _Todayweek - 1
RETURN
CALCULATE(
SUM('Sample'[Value]),
'Sample'[Week] = _Lastweek && YEAR('Sample'[Date]) = YEAR(TODAY()) && MONTH('Sample'[Date]) = MONTH(TODAY())
)
And then use these two measures to calculate the change:
Change = [Last Week] - [Last Month]
Change % = DIVIDE([Change], [Last Month])
Put the column Player Name and these four measures into the table visual:
If your dataset is different from my test dataset, could you please provide me with the sample data you used, thank you!
Also, if you are looking for a way to create two columns side by side at the same time, I am sorry to say that this is not possible, Power BI Desktop can only create one column or measure at a time unless you create a calculated table.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @N_Kotiyal ,
Please try this DAX:
CALCULATED_TABLE =
VAR CurrentWeek =
CALCULATE(
MAX('Sample'[Week]),
'Sample'[Date] = TODAY()
)
RETURN
SUMMARIZE(
'Sample',
'Sample'[Player Name],
"LastMonthValue",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"LastWeekValue",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"Change",
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
) -
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
),
"Change %",
DIVIDE((CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
'Sample'[Week] = CurrentWeek - 1 && MONTH('Sample'[Date]) = MONTH(TODAY()) && YEAR('Sample'[Date]) = YEAR(TODAY())
)
) -
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
)),
CALCULATE(
SUM('Sample'[Value]),
FILTER(
'Sample',
MONTH('Sample'[Date]) = MONTH(TODAY()) - 1 && YEAR('Sample'[Date]) = YEAR(TODAY())
)
)
))
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think this community is useless never help me in any doubt moreover ask useless question .
yes
Hi @N_Kotiyal,
You want to show only the columns Last Month, Last Week, change and change%?
Proud to be a Super User!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |