topic Re: Need filter to only get the highest selected year Year to Half year formula in DAX Commands and Tips

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

Invisibleman — Mon, 29 May 2023 08:47:35 GMT

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

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

Mahesh0016 — Mon, 29 May 2023 11:19:17 GMT

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!!

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

Invisibleman196 — Mon, 29 May 2023 11:41:24 GMT

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

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

Anonymous — Wed, 31 May 2023 08:51:34 GMT

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.

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

Invisibleman — Wed, 31 May 2023 17:05:56 GMT

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