cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BjornJorgensen
Frequent Visitor

calculate value between two chosen filtered months

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?

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

H9 @BjornJorgensen 
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

View solution in original post

8 REPLIES 8
BjornJorgensen
Frequent Visitor

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

@BjornJorgensen 
Yes, but what does that have to do with finding the two selected months? Gaps are irrelevant here.

BjornJorgensen
Frequent Visitor

that didn't work either 😞

 

what i have done so far which gets me a little on the way is the following:

Index = RANKX(Prognosemodell_Prognose_Total_Test_Historikk, Prognosemodell_Prognose_Total_Test_Historikk[prognose_dato], , ASC, Dense)
 
i.e. i have created an index for the month, which means the last month has the highest index and the oldest months have index 1.
 
then i used this to pick up the value for the highest index chosen - 1, i.e. i can see the difference between the highest chosen index and the next on the list.
 
inntekter forrige = CALCULATE (
    SUM ( Prognosemodell_Prognose_Total_Test_Historikk[Inntekter] ),
    FILTER (
        ALLEXCEPT ( Prognosemodell_Prognose_Total_Test_Historikk,date_dim[Date] ),
        Prognosemodell_Prognose_Total_Test_Historikk[Index]
            = MAX ( Prognosemodell_Prognose_Total_Test_Historikk[Index] ) - 1)           
    )
 
what would actually solve the problem is if i can set the -1 to the index of the min of the chosen months. any idea on how to do this?

Hi @BjornJorgensen 
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.

@BjornJorgensen 

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!
 

tamerj1
Super User
Super User

H9 @BjornJorgensen 
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
BjornJorgensen
Frequent Visitor

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:

BjornJorgensen_0-1661253618729.png

 

my problem is how to calculate the difference part, based on ANY chosen months in the filter.

bilalrana
Resolver I
Resolver I

Hi @BjornJorgensen 

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!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors