How can I create a measure for matrix table that shows all months up to the max date selected?

[santosonit]

Hello,

I am trying to have this visual show all values through the months of year until the month in the max date selected e.g. (if the date slicer has 09/01/2024 - 09/30/2024, then show all values from Jan through Sep.

This measure keeps showing values from 2023.

Note that Calendário[Date] filter pcprest[DTVENC].

TotalVALOR_YearMaxDate = 
CALCULATE(
    SUM(pcprest[VALOR]),
    FILTER(
        ALL('Calendário'[Date]),
        YEAR('Calendário'[Date]) = YEAR(MAX('Calendário'[Date])) && 
        'Calendário'[Date]<= MAX('Calendário'[Date])
    )
)

 Appreciate any help!

[v-kaiyue-msft]

Hi @santosonit ,

Have you already solved the problem? If so, can you share your solution here and mark the correct answer as standard to help other members find it faster? Thank you very much for your co-operation!

Best Regards,

Clara Gong

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

[OktayPamuk80]

Did you check my example? Did it help?

[v-kaiyue-msft · Accepted solution]

Hi @santosonit ,

According to your description, I have created two tables. There is no relationship between the two tables.

1. create measure.

MEASURE =
VAR _min_date =
MIN ( 'DAX DateTable'[Date] )
VAR _max_date =
MAX ( 'DAX DateTable'[Date] )
RETURN
IF (
YEAR ( MAX ( 'financials'[Date] ) ) = YEAR ( _max_date )
&& MAX ( 'financials'[Date] ) <= _max_date,

1
)

2. Drag it into the filter pane of the table's visual object to filter data with a value of 1.

3. create measure.

Measure2 =
CALCULATE (
SUM ( 'financials'[ Sales] ),
FILTER (
ALL ( 'financials' ),
YEAR ( 'financials'[Date] ) = YEAR ( MAX ( 'DAX DateTable'[Date] ) )
&& 'financials'[Date] <= MAX ( 'DAX DateTable'[Date] )
)
)

The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[OktayPamuk80]

Hi, 

I have here a demo report with that example:

https://drive.google.com/drive/folders/1-1hilkkcjEjc451A7XMAVJtU701LqY3W?usp=sharing

Can you check, why I am getting the expected result and you don't? You can confirm whether it is the same situation.

Regards,
Oktay

[Ashish_Mathur]

Hi,

Try this measure pattern

Measure = calculate(sum(Data[Amount]),datesbetween(calendar[date],date(year(max(calendar[date])),1,1),max(calendar[date])))

Hope this helps.

[OktayPamuk80]

Hi Santosonit,

Strange, so to check:

1) your calendar is connected to the pcprest table and is marked as date table (in table view select table and in the menubar there is the "mark as date table"

2) You didn't change the interactions (clicking on the slicer and under Format > Edit interactions you can configure so that the matric does not respond to the selected slicer)

3) Your requirement should also be achieved by using TotalYTD( sum(pcprest[VALOR], 'Calendario'[Date])

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[santosonit]

Thanks for your attention and help, @OktayPamuk80 .

That didn't solve it though.

I have taken the steps above, but still...

I need the selected year's month values to show until the max date in the date picker/slicer.

Cheers,

Antonio