Need filter to only get the highest selected year Year to Half year formula

[Invisibleman]

Dear all,

For my project I need to have the filter for the Year to Half year that is then the half year runing total, like the TotalMTD, TotalQTD & TotalYTD. However, when I am using the TotalMTD, TotalQTD or TotalYTD, by default it will show me, in the Line and Stack Column Chart, only the value of the highest selected year, when I am using only the month in the in the chart. With this Year to Half Year however, it's gives the summarized value for all selected years. 

Can anyone please help me, that this will work the same way as the TotalMTD, TotalQTD & TotalYTD, so it will then also only show the value of the highest selected year?

The formula that I got from ExcellsFun (YouTube) is;

Test Revenue Half Year to Date =

 IF(

        ISFILTERED(mapCalendar[Month]),

        CALCULATE(

            [Revenue (Base) (Selection EUR or USD)],

            FILTER(ALL(mapCalendar),MapCalendar[Date]<=MAX(mapCalendar[Date])),

            VALUES(mapCalendar[Year Part])

        )

    )

See also the test Power BI I created; Test HTD 

Thanks in advance,

Regards,

Hans

[Mahesh0016]

@Invisibleman 

Test Revenue Half Year to Date =

 IF(

        ISINSCOP(mapCalendar[Month]),

        CALCULATE(

            [Revenue (Base) (Selection EUR or USD)],

            FILTER(ALL(mapCalendar),MapCalendar[Date]<=MAX(mapCalendar[Date])),

            VALUES(mapCalendar[Year Part])

        )

    )

@Invisibleman i hope this help you!!

[Invisibleman196]

Hello Mahesh0016,

Thanks for the formula, but this doesn't change it, it is still showing the total of all selected years and I want to have to show only the highest selected year.

Regards,

Hans

[Anonymous]

Hi @Invisibleman ,

I couldn't find [Test Revenue Half Year to Date] in your sample. I think you also can try code as below.

Test Revenue Half Year to Date =
IF (
    ISFILTERED ( mapCalendar[Month] ),
    CALCULATE (
        [Revenue (Base) (Selection EUR or USD)],
        FILTER (
            ALL ( mapCalendar ),
            MapCalendar[Date] <= MAXX ( ALLSELECTED ( mapCalendar ), mapCalendar[Date] )
        ),
        VALUES ( mapCalendar[Year Part] )
    )
)

If this reply still couldn't help you solve your issue please share a sample with the measure you created and show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Invisibleman]

Hello Rico,

Sorry, but this formula is showing the total of the half year and not the highest selected year and then the total per month, like it also does with the MTD, QTD & YTD.

Also I am sorry, but it seems I had a link to the wrong file here's the correct file Correct file for Half Year to date 

The measure I am using is;

Revenue Half Year to Date =

    IF(

        ISFILTERED(mapCalendar[Month]),

        CALCULATE(

            [Revenue (Base) (Selection EUR or USD)],

            FILTER(ALL(mapCalendar),mapCalendar[Date]<=MAX(mapCalendar[Date])),

            VALUES(mapCalendar[Year Part])

        )

    )

And the result I want is, .

And regardless the years that are selected, it should only show those value in the chart for the highest selected year. And the year, should not be needed to add in the X-Axis, like it is also not needed with MTD, QTD & YTD.

Thanks in advance,

Hans