Issue with YTD calculation

[marieke]

Hi,

i have made a calculation to get monthly changes, so it is calculating this months values against previouw months values. The outcome for this calculation is working properly, so the values in column Actual are as expected.

But when I want to use these values into a YTD calculation, it is not working for some of the rows in the template.

I just don’t understand why the YTD totals are incorrect for some of them.

This is the DAX measure to get the monthly actuals:

Debiteuren = 
[Debiteuren Balans] 
- 
CALCULATE([Debiteuren Balans], PREVIOUSMONTH(dim_Calendar[Date])) 
*-1

and this for the YTD:

Debiteuren YTD =
CALCULATE([Debiteuren] , DATESYTD(dim_Calendar[Date]))

As you can see in attached printscreen, the actuals for “Debiteuren” should give a total of (-679984 + 275994 + 96549 + 43055 = ) -264386, but is is giving -944774.

For “Voorraden” the calculations are based on the same method and this one is okay. But for Crediteuren I also have issues with the YTD.

It is quit difficult to add the original report as there is a lot of other data in this report which I can not share. But I have attached a printscreen and hope somebody can point me into the right direction to get this solved.

Thanks for your help.

[Greg]

Hi @marieke. I would think that virtual table(s) and ISINSCOPE may help, but it’s hard to say without a PBIX. Given your difficulties in adding the original report, perhaps you could create a small sample dataset and PBIX that you can share that illustrates the issue, again with a printscreen or Excel mockup of the outcome you’re looking for.
Greg

[marieke]

Hi @Greg,

thanks for your quick reply, I understand it is difficult without the examples. I will work out a small dataset tomorrow or Wednesday and post it on the forum asap…

Marieke

[AntrikshSharma]

@marieke

[marieke]

Hi @AntrikshSharma,

Okay, so PREVIOUSMONTH is only returning the difference for 1 month. But if I select several months, it will show the individual differences between all these selected months, as it does in my columns 'Actuals". And then I wanted to use YTD to get the total for all these individual differences? Or is that not possible to do?

[marieke]

Hi, here is a small sample of the dataset.
So it would be great if somebody can help me to get this solved.

Dummy rapport.pbix (551.5 KB)

[Kumail]

Hello @marieke

Conceptually debtors balance gives running totals therefore, we don’t need to use DATESYTD to get the running total.

We can use the debtors balance measure in the monthly context that will give the debtor balance.

Now monthly debtors of January 2021 should be the increase from -237009 in Dec 2020 to 442974 in Jan 2021 which is 679983 correct in the monthly context given the fact that it should be the change in debtors from the last month i.e. Dec 2020.

The reference working pbix file is attached.

I hope this helps.

Regards
Kumail RazaDummy rapport_Updated.pbix (555.0 KB)

[marieke]

Hi Kumail,

many thanks, it is giving me the result I want so very great!

Regards,
Marieke