Showing year to date view of a measure (multiple months selected on a filter)

[Anonymous]

Hello, 

I have used the switch DAX function to use certain calculations when different months are selected via a filter eg) first half of the year uses one calcualtion and second half of the year uses a different calcularion. 

This works perfectly if i select one month at a time, however if I select multiple months it comes up with (Blank)... any suggestions what i need to do to make it show an aggregate view?

My formula is laid out like the following:

= SWITCH( SELECTEDVALUE(Months[Month]), "Jan", Result 1, "Feb", Result 2 etc etc etc 

Any help would be grately appreciated, thank you 

[AlB]

Hi @Anonymous 

It returns BLANK() because SELECTEDVALUE( ), by default returns blank if more than one item is selected. You'd need to explain exactly what behavior you need if more than one month is selected so that we can go into the details. One option would be:

SWITCH( SELECTEDVALUE(Months[Month], "Multiple months"), "Jan", Result 1, "Feb", Result 2, "Multiple months", Result 3 etc etc etc

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks so much for getting back to me, can you explain a little more about how I would incorporate the Multiple Months function into my formula?

My formula is really long/complex so for the purposes of this thread i'll show it like the following:

= SWITCH( SELECTEDVALUE(Months[Month]), "Jan", Result 1, "Feb", Result 1 ,  "Mar", Result 1, "Apr", Result 1 , "May", Result 1, "Jun", Result 1 , "Jul", Result 2, "Aug", Result 2,  "Sep", Result 2 "Oct", Result 2 , "Nov", Result 2, "Dec", Result 2)

So essentially one result (formula used) for the first half of the year and another result for the second half of the year, but if I select say May and October I want to show an aggregate view. I hope that makes sense, thanks again 🙂

[AlB]

@Anonymous 

Try this:

Measure =
VAR firstHalf_ =
    DATATABLE (
        "Month", STRING,
        {
            { "Jan" },
            { "Feb" },
            { "Mar" },
            { "Apr" },
            { "May" },
            { "Jun" }
        }
    )
VAR secondHalf_ =
    DATATABLE (
        "Month", STRING,
        {
            { "Jul" },
            { "Aug" },
            { "Sep" },
            { "Oct" },
            { "Nov" },
            { "Dic" }
        }
    )
VAR selectedMonths_ =
    DISTINCT ( Table1[Month] )
VAR isFirstHalf_ =
    COUNTROWS ( INTERSECT ( selectedMonths_, firstHalf_ ) ) > 0
VAR isSecondHalf_ =
    COUNTROWS ( INTERSECT ( selectedMonths_, secondHalf_ ) ) > 0
VAR isBothHalves = isFirstHalf_ && isSecondHalf_
RETURN
    SWITCH (
        TRUE (),
        isBothHalves, [Result 1-2],
        isFirstHalf_, [Result 1],
        isSecondHalf_, [Result 2]
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you @AlB !!

Sorry for the stupid questions but would i now not use the Switch/Selected Value part of the formula at all, or should the formula you just sent through be incorporated into what i already have? Thanks 🙂

[AlB]

@Anonymous 

That's the whole formula. I've taken a slightly different approach to account for all your requirements. Table1 is the table with the Months column you are using in the slicer. If you find problems adapting it,  share the pbix (if not confidential) and I'll have a look.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

What exactly is an "aggregate view"? What would be the formula in that case, Result1 + Result2?

So if i understand correctly what you need is,

- if the selected months (1 or more) all fall on the first half of the year, the apply Result1

- if the selected months (1 or more) all fall on the second half of the year, the apply Result2

- if the selected months (more than 1) fall on both the first and second half of the year, the apply "aggregate view" (still to clarify)

Is the above correct?

Do you have a column with the month numbers as well? Using that would probably be faster than using the names of the months

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you for replying so quick @AlB !!

Sorry what I mean by aggregate view is that the sum of the result 1 and result 2 are added together if multiple months are selected, eg if I select Jan and Feb then the result 1 of both of these months are added together. If I select Jan & Dec for example then sum of Result 1 & Result 2 would be added together for both months. I hope that makes sense?

But yes all of your points are correct, this is what I am trying to achieve!

The months data table is laid out like the following (I do think I have tried it with the numbers though and didn't seem to work, sorry am not very technical!!) :

Month    Index

Jan          1

Feb          2

Mar         3

Etc

Thanks again 🙂