Calculate() with specific Date filter

Anything related to PowerPivot and DAX Formuale
LeviGer
Posts: 1
Joined: Wed May 08, 2019 4:38 am

Calculate() with specific Date filter

Postby LeviGer » Wed May 08, 2019 4:45 am

Hello everybody!

I am having a pretty weird problem that I cannot seem to solve. It is weird because it seems to be a very basic problem.

Here is what I want to achieve: I want to write a measure that calculates the total revenue for a specific date (the specific date being the last day when a customer placed an order). Nothing easier than that, right?

Revenue:Last Day=CALCULATE([Revenue],Filter('Calendar','Calendar'[Date]=[Last Order Date]))


However, this does not work. This measure returns the total revenue for the entire period. That is, the output of this measure equals the [Revenue] measure.

I am pretty convinced that this must be a formatting-related problem. Simplifying the above code makes this pretty obvious in my opinion:

Revenue:Last Day=CALCULATE([Revenue],'Calendar'[Date]=30/12/2017)


This formula returns blank values.

The only way I can actually make this work is with the Date() function and using a static condition:

Revenue:Last Day=CALCULATE([Revenue],'Calendar'[Date]=date(2017,12,30))


This formula returns the correct numbers. But since this measure is static and not dynamic, it is not what I want.

I also tried to adjust the formats:

Revenue:Last Day=CALCULATE([Revenue],format('Calendar'[Date],"MM/DD/YYYY")=format(30/12/2017,"MM/DD/YYYY"))


Again, this is not working.

So I am left wondering how I can use calculate in combination with a specific date?

Bernard Heymans
Posts: 18
Joined: Wed Feb 28, 2018 12:18 am

Re: Calculate() with specific Date filter

Postby Bernard Heymans » Fri May 17, 2019 5:43 pm

Hi Levinger,

Just had a look,

for me this work:

as Decembre 1st 2018 = 43435

NS.01dec2018 :=CALCULATE(NS , ddate[dday]=43435)
but it show the same value for any month
NS.01dec2018 := CALCULATE( NS , filter( ddate , ddate[dday] = 43435 )

regards,

Bernard.

Bernard Heymans
Posts: 18
Joined: Wed Feb 28, 2018 12:18 am

Re: Calculate() with specific Date filter

Postby Bernard Heymans » Fri May 17, 2019 5:58 pm

Could this help?

Max_Date =if( [NS.] >0 , MAX('Data no customer'[Date - Calendar Year/Month]))

Attention: my accounting year is starting April.. month 1 =April

Image

Regards,

Bernard


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests