Calculation based on different accounting date

[kilala]

Hi I have created 1 measure to calculate YTD amount for 3 division, (1,2,3). However, these 3 division might have different accounting date compared to our normal date. So, in my dimdate, i hvae 4 columns as sample below:

Datekey	Date	PeriodKeyDiv1	PeriodKeyDiv2	PeriodKeyDiv3
20211228	2021/12/28	202201	202112	202112
20211229	2021/12/29	202201	202112	202201
20211230	2021/12/30	202201	202201	202201
20211231	2021/12/31	202201	202201	202201
20220101	2022/01/01	202201	202201	202201

Logically, if I want to calculate YTD amount for any division, I should start adding amount starting from 20220101.

However, that is not the case here. 

In my case, if I want to calculate YTD amount for div1, i need to sum the amount starting from 20211228 until today.

if I want to calculate YTD amount for div2, i need to sum the amount starting from 20211230 until today.

if I want to calculate YTD amount for div3, i need to sum the amount starting from 20211229 until today.

How do I do that using measure?

Please help:)

Currently I am only connecting datekey from facttable to datekey from dimdate.

[Anonymous · Accepted solution]

Hi @kilala,

I'd like to suggest you add segmentation table with these division and correspond date ranges.

Then you can map these divisions to your table records and write formula to calculate based on current division.(use current division to search on the segmentation table date range to filter in the formula calculations)

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng