Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
hi
i have a filter with 5 months values, lets say i have chosen 01.2022 and 03.2022.
i have an income column that contains income value for all dates within each month.
The problem:
i need to sum up income for all days in 01.2022 and 03.2022 and calculate the difference between the two months, i.e. income for max month - income for min month.
this should work with whatever 2 months i choose in the filter.
i have googled and couldn't find any solutions to this problem.
is this even possible?
Solved! Go to Solution.
H9 @Anonymous
Please try
Difference =
VAR SelectedMonths =
ALLSELECTED ( TableName[Month] )
VAR MaxMonth =
MAXX ( SelectedMonths, TableName[Month] )
VAR MinMonth =
MINX ( SelectedMonths, TableName[Month] )
VAR MaxValue =
CALCULATE ( [Income], TableName[Month] = MaxMonth )
VAR MinValue =
CALCULATE ( [Income], TableName[Month] = MinMonth )
RETURN
MaxValue - MinValue
the reason why i have to use index for this is that i have missing months in between, so the previousmonth etc functions won't work
@Anonymous
Yes, but what does that have to do with finding the two selected months? Gaps are irrelevant here.
that didn't work either 😞
what i have done so far which gets me a little on the way is the following:
Hi @Anonymous
It depend on the filter context of the visual itself. I can see that all columns are from the same table. Not having dimention tables might be little tricky. The solution I have provided earlier (if the month column is a numeric value column) should work, however, depending on the existing filter context, the filter of some columns might have to be removed. Please provide more context in order to support you further. Thank you.
@Anonymous
You can use Variables in the DAX Measures.
Helping Link: https://docs.microsoft.com/en-us/dax/best-practices/dax-variables
As communicated by @tamerj1 , you can cater this scenario.
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
H9 @Anonymous
Please try
Difference =
VAR SelectedMonths =
ALLSELECTED ( TableName[Month] )
VAR MaxMonth =
MAXX ( SelectedMonths, TableName[Month] )
VAR MinMonth =
MINX ( SelectedMonths, TableName[Month] )
VAR MaxValue =
CALCULATE ( [Income], TableName[Month] = MaxMonth )
VAR MinValue =
CALCULATE ( [Income], TableName[Month] = MinMonth )
RETURN
MaxValue - MinValue
i can't see how that will solve my problem.
i have a table visual with month and total_income and difference.
it should look like the following:
my problem is how to calculate the difference part, based on ANY chosen months in the filter.
Hi @Anonymous
For this you can use the Date Filter to set Max Min Range of Dates. Follow the instructions from the link:
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
33 | |
25 | |
24 | |
23 |