Calculating LYMTD,YTD, etc base on the specific month / year / period column

[Retrorock]

We have a sales table that comes with a date column, normally we would just create a date table and link it to that for time intelligence calculations, but we couldn't in this case.

That's because this is a table with multiple countries, and for each different country they have different start dates / end dates for their sales period. A custom date table doesn't seem to work because the dates would not be unique keys (say we have 4 countries with their different period, the table would have 4 of each date).

Is there a way to use time inteligence that forces it to use the specified month / year in the source table? or maybe there is another smarter way to do this?

Here is a sample of the file so you can see what I mean here.

https://drive.google.com/file/d/1EgGjttgGkRmCdqE9DFyA8TeZpPkGeHi6/view?usp=sharing 

[lbendlin]

I assume you mean this

That means instead of the usual time intelligence you would have to count the number of days in the current period and then compare against the same number of days in the prior period.  Another option would be to extend your calendar table with the month, quarter and year buckets for each country as separate columns.

There is one more problem with your source data

Please check if that is intended or not.

[Retrorock]

---

@lbendlin wrote:

I assume you mean this

That means instead of the usual time intelligence you would have to count the number of days in the current period and then compare against the same number of days in the prior period.

---

Yea pretty much what I mean, is there a way to do this? I'm not sure how to begin writing the dax for this, is there a sample of the expression that should be used? 

Extending the calendar table doesn't work because the stakeholder wants to see the numbers by the whole period.

---

@lbendlin wrote:

There is one more problem with your source data

Please check if that is intended or not.

---

Thanks for pointing that out, it's not the real data but i should probably take a better look at the raw. 

[lbendlin]

Extending the calendar table doesn't work because the stakeholder wants to see the numbers by the whole period.

That's exactly what extending the calendar table would accomplish.

[Retrorock]

so let's say I extended the calendar table to include something like this 

how would I write the dax for MTD to make sure when I filter for 7/1/2022 MTD it would include the sales for Aus and Alas 6/30/2022 but not Bal and Ita 6/30/2022. If i used the usual MTD calculation like CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) it would calculate using normal month

[lbendlin]

that's correct, you need to implement your own MTD logic - sense the country that is currently in filter context and then grab the appropriate column. You need to do that anyway as I mentioned before.