Multiple values error in calculate DAX

[NLRI_78]

Hi there, 

This is my query on here, no doubt the first of many! 🙂

This should be a straightforward query to answer but any help will be greatly appreciated.

I've used the following DAX in previous reports but for some reason it is now working on my latest report. I have a line item report from our SAP system of all financial transactions for the year, I simply want to summarise it and then filter based on the period selected in order to update a YTD calculation.

The DAX I used is - 

YTD Actuals = CALCULATE(sum('New FPR'[Actuals]),'New FPR'[Fund Center Group] <> "Admin-Prog Hierarchy",'New FPR'[Dates.Period] <= VALUES('Date Updated'[Period]))

The New FPR is the SAP transactions, the Date Updated table is simply a 1 row table with the period I've updated the report to (so in this instance, it is period 12 but in future it will be 1, 2, 3 and so on. 

As I say, this has worked in the past and I simply update the date updated table and all of my DAX updates accordingly without the need to amend individually, so my run rate calculations etc.

Any help would be really appreciated. 

Thanks in advance

[bcdobbs]

Based only on your DAX my guess would be that 'Date Updated' currently has more than one row:

YTD Actuals =
CALCULATE (
    SUM ( 'New FPR'[Actuals] ),
    'New FPR'[Fund Center Group] <> "Admin-Prog Hierarchy",
    'New FPR'[Dates.Period] <= VALUES ( 'Date Updated'[Period] )
)

See if this works:

YTD Actuals =
CALCULATE (
    SUM ( 'New FPR'[Actuals] ),
    'New FPR'[Fund Center Group] <> "Admin-Prog Hierarchy",
    'New FPR'[Dates.Period] <= MAX ( 'Date Updated'[Period] )
)

[NLRI_78 · Accepted solution]

Thanks so much, that worked perfectly. No idea why it thought there was more than one row but clearly there must have been. 

Thanks again, really appreciate your help 🙂