workarounds for month and time filtering where no date is supplied

[bungle]

I have a problem I am trying to work around. Basically the data I am working with does not have a date column, it comes with H1, H2, Q1-4 + month 1-12. Most of the measures we are using are hacky at best where we are trying to calculate YTD or MoM, YoY actuals because we don't have a date in th data that we can use as now to work back from. 

In my mind we need to figure a way that we make a measure that takes the month number from the data, compares it to the month number of todays date and then remove n-1 for whatever measure we need to look for. 

Are we barking up the wrong tree here and is this likely possible? I have asked for dates in the data already but if that's not going to materialise we need a report that we can slice through without needing multiple visuals for each measure for each period where the slicer would do that automatically normally.

Thanks

[Chris_White]

Oh, your biggest problem then is that you don't have a year column.  I assume the data you get is less than 12 month's worth, otherwise if May-2022 and May-2023 are in the same file, then they're indistinguishable?

One idea which might work for you is to set up a yearly process where in January you create a file for the previous year.  Then you only take data for this calendar year from your current file & append the two together in Power Query.

[Chris_White]

Hi,
As I understand it, your data does not specify day of month.  Therefore, could you create a date column by assigning everything to the 1st of the month and then work from there?  This would give you a date column to work with rather than trying to handle separate month/years (you don't say that your data has a year column, but I assume it must).

This would then allow you to use the built-in time intelligence functions in DAX rather than trying to make your own way from scratch.

[bungle]

Hi Chris, thanks for responding. The data doesn't even have a year in it, only H1/H2, the quarters and a column named Month that has the month number inside it. In truth it's just not all that helpful in the format it is in but we have to work with what we have until it ever changes.

We did recentl get an updated file where we asked for dates, however this was produced only with a column filled with the same date - a timestamp on when it was created really. My most recent thought to workaround this was in a similar vain to what you said, use that date column to specify MoM as the supplied date - 4 weeks, forecasts as everything beyond that date, however I think for that to work we would probably also need to map the numerical month somehow.

Ideally, as you say we want to use time intelligence that is built in, instead of the very hacky workarounds we are trying to come up with.