Need a help for Last Year YTD Data by Month selection

[Srinivas904]

Hi Everyone,

In the screenshot below, I am using CY (Current Year) and PY (Previous Year) cards to display values for the Current Year YTD (Year-To-Date). The measure works fine for CY. If I select January, it shows the current year's January value, and if I select February, it sums up January and February values for the CY, which is correct.

However, the PY YTD is not working as expected. When I select January, it should show the previous year's January value, and if I select February or March, it should sum up the previous months and show the value accordingly. I tried changing the formula similar to CY YTD for PY YTD, but it is not working. Currently, the PYYTD is just showing revenue up to the same date last year minus one day, but it needs to work with month selection. if I dont select any month it just have to show Last year YTD

Can anyone please help me fix this? The PYYTD should show the correct values based on month selection.

Measure used for CYYTD & PYYTD

YTD =

CALCULATE(

    [GBP/1000],

    DATESBETWEEN(

        DIM_TIME[Full_Date],

        DATE(YEAR(TODAY()), 1, 1),

        MAX(DIM_TIME[Full_Date])

    ),

    ALLSELECTED(DIM_TIME[FIN_MONTH])

)

||----------------------------------------------------------------------------------------------------------------------------------||

LYYTD = CALCULATE([GBP/1000],DATESBETWEEN(DIM_TIME[Full_Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)-1))

Thanks

Srinivas

[v-linyulu-msft · Accepted solution]

HI,@Srinivas904 

Regarding the issue you raised, my solution is as follows:

Based on your code, we infer that this is caused by a conflict between the slicer and the dax, so we recommend that you create a separate time column as the slicer.

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

Table 2 = VALUES('DIM_TIME'[Date])

And use the month as a slicer.

3. Below are the measure I've created for your needs:

YTD = 
 CALCULATE(SUM(DIM_TIME[values]),DATESBETWEEN('DIM_TIME'[Date],
        DATE(YEAR(TODAY()), 1, 1),
        MAX('Table 2'[Date])
    ))

PTD = 
VAR kk =
    EOMONTH ( MAX ( 'Table 2'[Date] ), 0 )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Date].[Month] ),
        CALCULATE (
            SUM ( DIM_TIME[values] ),
            DATESBETWEEN (
                'DIM_TIME'[Date],
                DATE ( YEAR ( TODAY () ) - 1, 1, 1 ),
                DATE ( YEAR ( TODAY () ) - 1, MONTH ( kk ), DAY ( kk ) )
            )
        ),
        SUM ( DIM_TIME[values] ))

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[v-linyulu-msft · Accepted solution]

HI,@Srinivas904 

Regarding the issue you raised, my solution is as follows:

Based on your code, we infer that this is caused by a conflict between the slicer and the dax, so we recommend that you create a separate time column as the slicer.

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

Table 2 = VALUES('DIM_TIME'[Date])

And use the month as a slicer.

3. Below are the measure I've created for your needs:

YTD = 
 CALCULATE(SUM(DIM_TIME[values]),DATESBETWEEN('DIM_TIME'[Date],
        DATE(YEAR(TODAY()), 1, 1),
        MAX('Table 2'[Date])
    ))

PTD = 
VAR kk =
    EOMONTH ( MAX ( 'Table 2'[Date] ), 0 )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Date].[Month] ),
        CALCULATE (
            SUM ( DIM_TIME[values] ),
            DATESBETWEEN (
                'DIM_TIME'[Date],
                DATE ( YEAR ( TODAY () ) - 1, 1, 1 ),
                DATE ( YEAR ( TODAY () ) - 1, MONTH ( kk ), DAY ( kk ) )
            )
        ),
        SUM ( DIM_TIME[values] ))

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Srinivas904]

HI @v-linyulu-msft , will it work with 4-4-5 calendar, because we are using financial calender 

[v-linyulu-msft · Accepted solution]

Hi,@Srinivas904 

Regarding the issue you raised, my solution is as follows:

My current scheme doesn't work with 4-4-5 calendar, but you can use the DATE () function to apply the CALENDAR () function, generate a new table as the date table used by DAX, and then relate this new table to the 4-4-5 calendar table. This will work on the 4-4-5 calendar.

1.Here are the relevant calculations table:

main Table = CALENDAR(DATE(2024,3,19),TODAY())

2.Here are some relevant documents to help you:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

CALENDAR function (DAX) - DAX | Microsoft Learn

DATE function (DAX) - DAX | Microsoft Learn

Best Regards,

Leroy Lu

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