YTD Logic and filtering dates

[PhMeDie]

Hi,

I need your help on below.

I have two tables, a date table and fact table.

I'd like to implement a simple (custom) YTD logic to sum up YTD sales.

See below formula.

Sales YTD = CALCULATE (

sum('Table'[Sales Quantity]),

FILTER (

Calendar_day,

AND (

Calendar_day[Calendar_day.Commercial Year]

= MAXX (

FILTER ( Calendar_day, Calendar_day[Calendar_day.Calendar Date] = Today() ),

Calendar_day[Calendar_day.Commercial Year]

), Calendar_day[Calendar_day.Commercial Year_Week]

<= MAXX (

FILTER ( Calendar_day, Calendar_day[Calendar_day.Calendar Date] = Today() ),

Calendar_day[Calendar_day.Commercial Year_Week]

)

)

)

)

The formula is giving me the right results, but then when I try for example to drag in the calendar date to see details by date, the table remains blank.

What am I doing wrong?

I hope you can help.

Best,

P.

[v-lid-msft]

Hi @PhMeDie ,

We can try to use the following measure to meet your requirement:

Sales YTD =
CALCULATE (
    SUM ( 'Table'[Sales Quantity] ),
    FILTER (
        Calendar_day,
        AND (
            Calendar_day[Calendar_day.Commercial Year]
                = CALCULATE (
                    MAX ( Calendar_day[Calendar_day.Commercial Year] ),
                    FILTER (
                        ALLSELECTED ( Calendar_day ),
                        Calendar_day[Calendar_day.Calendar Date] = TODAY ()
                    )
                ),
            Calendar_day[Calendar_day.Commercial Year_Week]
                <= CALCULATE (
                    MAX ( Calendar_day[Calendar_day.Commercial Year_Week] ),
                    FILTER (
                        ALLSELECTED ( Calendar_day ),
                        Calendar_day[Calendar_day.Calendar Date] = TODAY ()
                    )
                )
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[PhMeDie · Accepted solution]

Hi,

thanks for your reply.

It almost worked. In the end the following code did the trick. I added another ALLSELECTED and then it worked. I cannot explain exactly why, but am happy 🙂

Best,

P

YTD =
CALCULATE (
    SELECTEDMEASURE (),
    ALLSELECTED ( Calendar_day ),
    FILTER (
        Calendar_day,
        AND (
            Calendar_day[Calendar_day.Commercial Year]
                = CALCULATE (
                    MAX ( Calendar_day[Calendar_day.Commercial Year] ),
                    FILTER (
                        ALL ( Calendar_day ),
                        Calendar_day[Calendar_day.Calendar Date] = TODAY ()
                    )
                ),
            Calendar_day[Calendar_day.Commercial Year_Week]
                <= CALCULATE (
                    MAX ( Calendar_day[Calendar_day.Commercial Year_Week] ),
                    FILTER (
                        ALL ( Calendar_day ),
                        Calendar_day[Calendar_day.Calendar Date] = TODAY ()
                    )
                )
        )
    )
)