cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattknight1986 Frequent Visitor
Frequent Visitor

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

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.

TOTALYTD fail.png

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.

TOTALYTD relationships.png

 

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

 

 

7 REPLIES 7
Interkoubess Established Member
Established Member

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

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

mattknight1986 Frequent Visitor
Frequent Visitor

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

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?

Interkoubess Established Member
Established Member

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

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 Frequent Visitor
Frequent Visitor

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

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 Established Member
Established Member

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

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 Frequent Visitor
Frequent Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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

 

TIA

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors