Year to Date & Previous Year/Year to Date ish, without dates

[ANJ]

To be brutally honest, I'm new to Power BI/Power Query and the M/DAX languages and the issue is probably more down to that.

I have a dataset:

Group1	Group2	Year	Week	Value
ABC	123	2023	1	100
ABC	123	2023	2	150
ABC	123	2024	1	50
ABC	567	2023	1	500
XYZ	123	2023	2	250
XYZ	123	2024	1	1000

I'm trying to create a paginated report, grouping on the group columns for a particular no. of years & weeks.  So in this case lets just say we're looking at the whole of 2024.  So the data is filtered where Year = 2024.

That's simple enough, however next to the Value column we also want to show the previous year for the same period and also the previous whole year.

So I'd hope to see:

Group1	Group2	Value	Prev. YTD	Prev. Year
ABC	123	50	100	250
ABC	567	0	500	500
XYZ	123	1000	250	250

I've seen a no. of forum posts talking about year to date etc. but they all talk about dates, which of course I don't have here.

1. Is this even possible without dates?

2. I could synthesis a date for each week as I do know which dates those weeks represent, but they don't necessarily match year on year of course.

Even some pointers to some documentation/tutorials would be helpful.  As this is all new to me.

[v-junyant-msft · Accepted solution]

Hi @ANJ ,

@jgeddes , good answer! 
And you can try DAX below.

First of all I'm a bit confused, I don't think I can find the corresponding value over here, is this a typo on your part or something?

And you can use these DAXs to create two new columns:

PrevYTD = 
CALCULATE(
    SUM(Sheet4[Value]),
    FILTER(
        ALL(Sheet4),
        'Sheet4'[Group1] = EARLIER(Sheet4[Group1]) && 'Sheet4'[Group2] = EARLIER(Sheet4[Group2]) && 'Sheet4'[Year] = EARLIER(Sheet4[Year]) - 1 && 'Sheet4'[Week] = EARLIER(Sheet4[Week])
    )
)

PrevYear = 
CALCULATE(
    SUM(Sheet4[Value]),
    FILTER(
        ALL(Sheet4),
        'Sheet4'[Group1] = EARLIER(Sheet4[Group1]) && 'Sheet4'[Group2] = EARLIER(Sheet4[Group2]) && 'Sheet4'[Year] = EARLIER(Sheet4[Year]) - 1
    )
)

The results are as follows:

However, this method cannot realize this line of data because the corresponding data for 2024 ABC & 567 does not exist and will not be displayed in the visual object:

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

[jgeddes]

You can do what you need without dates.
Here is some sample code you can paste into the advanced editor of a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyBpJGBmDKEIQNDJRidbBKG4EETLFKm0B145YFa7awQJI2NTNHtdoUanVEZBQ2q41MsUqbIBwOlI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group1 = _t, Group2 = _t, Year = _t, Week = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group1", type text}, {"Group2", Int64.Type}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type", "yearToDate", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> [Group1] = x[Group1] and [Group2] = x[Group2] and [Year] = x[Year])[Value]), type number),
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "previousYearTotal", each List.Sum(Table.SelectRows(#"Added Custom3", (x)=> [Group1] = x[Group1] and [Group2] = x[Group2] and [Year]-1 = x[Year])[Value]), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "previousYearWeek", each List.Sum(Table.SelectRows(#"Added Custom3", (x)=> [Group1] = x[Group1] and [Group2] = x[Group2] and [Year]-1 = x[Year]and [Week] = x[Week])[Value]), type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "previousYearWeekToDate", each List.Sum(Table.SelectRows(#"Added Custom3", (x)=> [Group1] = x[Group1] and [Group2] = x[Group2] and [Year]-1 = x[Year] and [Week] >= x[Week])[Value]), type number)
in
    #"Added Custom2"

This will get you 'yearToDate', 'previousYearTotal', previousYearWeek' and 'previousYearWeekToDate'.

It will look like...

Hopefully this gets you pointed in the right direction.