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

Dax for YTD to financial year with conditional aggregation is not working, please help

Hi Guys, 

I am trying to get YTD dax working to produce a YTD P&L,

I wrote following codes for measure Actual:

YTD Actual =
Var currentitem =SELECTEDVALUE('PL'[Items])
var currentitem2 = SELECTEDVALUE('PL'[Items2])
return
SWITCH(True(),
currentitem = "Revenue",[YTD Revenue],
currentitem = "Direct Costs",[YTD COS],
currentitem = "Total GP",[YTD GP],
currentitem = "Total Margin",FORMAT([YTD Margin],"0.00%"),
currentitem = "Total Expense",[YTD Expenses],
currentitem = "EBITDA",[YTD EBITDA],
currentitem2 = "GP",CALCULATE([YTD GP],FILTER('Consolidated',Consolidated[Account Cat]=currentitem)),
currentitem2 = "Margin",FORMAT(CALCULATE([YTD Margin],FILTER('Consolidated',Consolidated[Account Cat]=currentitem)),"0.00%"),
CALCULATE([YTD Total],FILTER('Consolidated',and(Consolidated[Account Cat]=currentitem,Consolidated[PL CAT]=currentitem2))))
Now in the above measure, the text coloured in green all working, I tested all the measures in green, they all show the cumulative values. and the measure [YTD Total] also working for YTD:, the measure [YTD Total] is below:
YTD Total = CALCULATE([Total],DATESYTD(Dates[Date],"31/03"))
measure [Total] is also working as below:
Total =
var PLAmount = sum(Consolidated[Amount])*-1
var BSAmount = sum(Consolidated[Amount])
return
if(SELECTEDVALUE(Consolidated[Type])="PL",PLAmount,BSAmount)
 
But when I run YTD Actuals, the last line CALCULATE([YTD Total],FILTER('Consolidated',and(Consolidated[Account Cat]=currentitem,Consolidated[PL CAT]=currentitem2))) brings me the same value as periodic value, I just couldn't figure out why? surely this is just a normal sumifs statement, how come it doesn't work?
 
Could anyone shed light on this as it is driving me mad.
2 REPLIES 2
Super User IV
Super User IV

Re: Dax for YTD to financial year with conditional aggregation is not working, please help

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rs1249 Frequent Visitor
Frequent Visitor

Re: Dax for YTD to financial year with conditional aggregation is not working, please help

Hi Greg, thanks very much for the reply, in the end, I found what the issue was, it was the filter that did not working in calculate, so I get rid of it, with usong straight forward condition, it worked, 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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 Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors