Forum Discussion
How to show data divided by future months
- 4 years ago
Grazmeister , refer if this blog can help
Grazmeister , refer if this blog can help
- Grazmeister4 years agoRegular Visitor
Hello amitchandak , thanks for stopping by.
Well, I followed the post you provided but i sadly doesn't meet what I need, or I didn't manage to get it properly working.
So after I adapted your formulas I end up getting this result:And the data I have is this:
As you can see, I need to show a visual with Month/Year (your first example) but with the amount paid divided by the duration of the stay.
As an example:
1. First line shows €1.941,00 paid in 11/2021 that's valid until the beggining of 12/2021 (this amount goes into the 11/2021 pot);2. The second line likewise;
3. Third line, €2.835,40 paid in 12/2021 that's valid until the beggining of 01/2022 (this amount goes into the 12/2021 pot)
4. And so on.
5. The problem is the 5th line: someone paid 600€ for 12 months, starting in 01/2022 and ending at the beggining of 01/2023.
I need to show 50€ (=600/12) every month since it was paid (50€ in 01/2022, 50€ in 02/2022, etc.).
Maybe i'm missing something?
(I deleted the relationship between the DATE table and the FACT table since it messed the crossjoin DAX you so kindly provided.)Thanks!
- Grazmeister4 years agoRegular Visitor
I got to play around your post a bit and managed to get the 600€ as this:
So, maybe it's just the days that are messing this up.
Thanks in advance.
- Grazmeister4 years agoRegular Visitor
Thanks, amitchandak !
Your solution worked!
I needed to add an Index column so it could treat each line distinctively and needed to change the comparison of the end date (Saída Data) to "<" instead of "<=".The formula which worked is this:
Value by day of Month =CALCULATE(SUMX(SUMMARIZE(FILTER(CROSSJOIN('Conceitos Cobrados','DATAS_fitas'),'DATAS_fitas'[Date] >= 'Conceitos Cobrados'[Entrada Data] && 'DATAS_fitas'[Date] < 'Conceitos Cobrados'[Saída Data]),'Conceitos Cobrados'[Index],'DATAS_fitas'[Date],'DATAS_fitas'[YearMonthnumber],'Conceitos Cobrados'[Valor],'Conceitos Cobrados'[Entrada Data],'Conceitos Cobrados'[Saída Data]),DIVIDE('Conceitos Cobrados'[Valor],DATEDIFF('Conceitos Cobrados'[Entrada Data],'Conceitos Cobrados'[Saída Data],DAY))))Thanks for your help!