WTD, MTD and YTD in one table based on filter slicer

[EitanKGCS]

Hi All,

I am sure this is something that must have been answered already but I am struggling to find what I need.

I am looking to create a report based on data pulled from a SQL server which will have YTD, MTD and WTD information. 

The data can be pulled if I select the date ranges on a filter slicer. But what I would like to do is from one data column create multiple measures that when selecting the slicer have different effects.

For example, if I select Feb'22 on the filter, I would like one column to show the whole of February (MTD), and the YTD column to show from the start of the year to the end of Feb so if I change to march it will change the MTD column to March but just extend the YTD column to include March. Is this possible? I can't seem to work out how to do it as the filter slicer just selects the same data values for both columns no matter what kinds of measures and filters I add. 

If possible it would also be great if I could add a WTD column that if I select a week will show WTD, MTD and YTD as well. 

Inv-Ytd = 
CALCULATE(
	SUM('RED'[Inv-Mtd]),
    RED[FinYear]=2022)

Thanks,

[v-kkf-msft · Accepted solution]

Hi @EitanKGCS ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please try the following code.

MTD: SELECTEDMEASURE ()
YTD: CALCULATE (
        SELECTEDMEASURE (), 
        ALL ( 'Dates' ),
        DATESYTD ( 'Dates'[Date] )
    )
WTD: LASTNONBLANKVALUE (
        'Table'[Date],
        SELECTEDMEASURE ()
    )

LMTD: CALCULATE (
        SELECTEDMEASURE (),
        ALL ( 'Dates' ),
        SAMEPERIODLASTYEAR ( 'Dates'[Date] )
    )
LYTD: CALCULATE (
        SELECTEDMEASURE (),
        ALL ( 'Dates' ),
        SAMEPERIODLASTYEAR ( DATESYTD ( 'Dates'[Date] ) )
    )
LWTD: CALCULATE (
    SELECTEDMEASURE (),
    FILTER (
        ALL ( Dates ),
        Dates[WeekNum] = MAX ( Dates[WeekNum] )
            && Dates[Year]
                = MAX ( Dates[Year] ) - 1
    )
)

Best Regards,
Winniz

[EitanKGCS]

Thanks for this suggestion @v-kkf-msft 

I have copied this to my report but it can't find the relationship between my fact table and the calculation group. I assume it is because I don't have a full dates table on y fact table due to only being able to pull week ending dates?

[v-kkf-msft]

Hi @EitanKGCS ,

If you also want to calculate last year's values, then I think a complete date table would be a great help in the calculation.

Best Regards,
Winniz

[DataInsights]

@EitanKGCS,

This can be achieved with a calculation group. You'll need to install Tabular Editor to create a calculation group. The calculation group will contain multiple calculation items (YTD, MTD, WTD), and these calculation items can be displayed as matrix columns (for example).

https://www.sqlbi.com/calculation-groups/ 

[EitanKGCS]

Thanks for this @DataInsights,

That was really helpful to understand. I just realised that the main thing stopping me from comparing YoY though is that the date table I am pulling from our SQL server is only the week-ending date. The problem with this is that last year's week is one day ahead of this year. So the same week this year ending 27/03/2022 was 28/03/2021. 

I have created a column called PrevYear:

PrevYear = EDATE(RED[PayrollDate],-12)+1

Which calculates the date of last year's week ending. However, I am not sure how to now use this column to connect last year's sales to it. Do you have any ideas on how I can do this?

Thanks in advance!

[DataInsights]

@EitanKGCS,

I recommend creating a robust date table with daily grain (there are many examples in forums and blogs that you can follow). This is a crucial part of any data model, and makes date calculations much simpler. The date table should contain the columns Week Ending Date, Week Number of Year, Year, etc. Once you create a date table, mark it as a date table, and create a relationship between the date table and fact table. Then, you can write DAX that filters for the same Week Number of Year for each year, and calculate the delta.