[SORRY] Can't get TOTALYTD to work...

[mattknight1986]

First of all, an apology - I know there are millions of these littered around, and I've read what feels like the majority of them. Yet I still can't get my TOTALYTD working properly... (Disclaimer: coming from an extensive Excel background, probably means my brain is [yet] not wired correctly for this brave new (well, for me) world...)

I have financial ledger data (financial year April to March), meaning the entries in the table correspond to the sum of all transactions for the specific cost centre and cost type for that month (i.e. there aren't any transaction dates). So I've created transaction dates by parsing the period number and financial year columns to create a column returning the first day of the respective month. Simple enough.

However, when I  create my YTD measures, the answers I get are weird and incorrect. For some reason the YTD measures are exactly equalling the in-month values and I can't figure out why (I assume it's something frustratingly simple...)

e.g.

My data structure is below. I had to create the PeriodMap table to get a unique pairing of calendar months to period numbers to then link to the TrialBalance table.

And finally, the DAX for my YTD measures is: 

YTDBudget = totalytd([SumofBudget],'Calendar'[Date],all('Calendar'[Date]),"31/3")

where SumofBudget is:

SumofBudget = sum(TrialBalance[Period Budget SUM])

(yes, I tried doing the SumofBudget bit by doing "TOTALYTD=(sum(budget),...etc etc" and that had the same results...)

About ready to defenestrate my laptop, so any guidance in helping me figure this out (and presumably in the process all future such queries), would be gratefully received.

Many thanks

Matt

[Interkoubess]

Hi @mattknight1986,

Please link (directly) your calendar to your fact table and then try again your measure.

You can also try the following measure for YTD:

YTDBudget = calculate([SumofBudget],Dateytd('Calendar'[Date],"31/3"))

Let us know if it does not work, we can still assist...

Ninter

[Interkoubess]

Hi @mattknight1986,

For the end date "31/3" could you check with "3/31".

I confirm that you can use YTD with previous year. I usede it a lot and it worked.

let us know of give us dummy data in order to simulate the outcomes.

thank you.

Ninter

[mattknight1986]

No dice, I'm afraid... No other string variation representing 31 March works either.

I've attached a sample of the data I'm using. I dod get errors on loading the query, but on investigation this looks to be importing #NAs from the source spreadsheets which are referencing invalid vlookups on my client's servers.

TIA

Onedrive links to data: TrialBalance, Calendar

[Interkoubess]

Hi @mattknight1986,

I downloaded the file and I could not find a date( with day) in it.

This is how we link calendar to the fact table> I see period but we cannot link it to the calendar because the time series functions work with continuous date (day).

Let us know if you missed something by sharing the dummy data.

Thank you.

Ninter.

[mattknight1986]

Thanks for bearing with me. I didn't miss anything with the dummy data, but as you rightly point out, there's no Date field in the trial balance table.  What I did overlook is the calculated columns I needed to include:

MonthName = FORMAT(DATE(2016,if(TrialBalance[Period]<10,TrialBalance[Period]+3,TrialBalance[Period]-9),1),"MMMM")

and:

Date = datevalue("01/"&TrialBalance[MonthName]&"/"&TrialBalance[CalYear])

which I;m using to parse the Period field into the first day of the relevant calendar month to make everything else work.

Any help, as always, much appreciated!

[mattknight1986]

 Was anyone able to shed any light on the issue I've bene having, at all, please?

TIA

[mattknight1986]

Thanks @Interkoubess, but I'm ending up with the same outputs. Also, the problem I have with the CALCULATE(sum, DATESYTD) approach is that it only works on current year dates, if I'm correct. (I know you can work around this, but I;m keen to get ANYTHING working for the moment), Am I correct in thinking that TOTALYTD works regardless of the year?