Forum Discussion
Calculation based on different accounting date
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.
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
1 Reply
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