FILTER DATE IN DAX

[Steph83]

Good day,

I have a measure that calculate the number of ressources needed based on a number of vessels ([NBR VESSELS GC]) . The number of vessels is increasing on a monthly basis.

NB ENGINEER NEEDS  = if([NBR VESSELS GC]>52,14,if([NBR VESSELS GC]>48,13,if([NBR VESSELS GC]>44,12,if([NBR VESSELS GC]>40,11,if([NBR VESSELS GC]>36,10,if([NBR VESSELS GC]>32,9,if([NBR VESSELS GC]>28,8,if([NBR VESSELS GC]>24,7,if([NBR VESSELS GC]>20,6,if([NBR VESSELS GC]>16,5,if([NBR VESSELS GC]>12,4,if([NBR VESSELS GC]>8,3,if([NBR VESSELS GC]>4,2,if([NBR VESSELS GC]>1,1,0))))))))))))))

I need to create a measure that calculate the number of ressources needed on the 01/01/2024 minus the ressources needed on the 01/01/2023. 

I tried with CALCULATE but I have an error (True/False expression does not sepcify a column)

 Needs 2023 = CALCULATE([NB GC ENGINEER NEEDS MRS],DATE(2024,01,01))- CALCULATE([NB GC ENGINEER NEEDS MRS],DATE(2023,01,01))

I'm fumbling and I am at the lowest level of knowledge in DAX but I do not despair of progressing!

Thanks for your support

[vanessafvg]

you will need to provide some sample data, as well as what your expected result from that sample data is.

this is not enough information without understanding how your data is structured.

I also think using a switch statement for your nested if statements is recommended because the readability is very difficult with what you doing.   However if you provide sample data with your expected result from that data, there might be a easier way to calculate this.

[Anonymous · Accepted solution]

@Steph83 , the error means that the Power BI does not know what to compare the DATE value against. What is the Date field in the fact table? For an example, if your fact table is called 'FactVessels' and the date field is [Date], then your measure might work something like this:

Needs 2023 = CALCULATE([NB GC ENGINEER NEEDS MRS],'factVessels'[Date] = DATE(2024,01,01))- CALCULATE([NB GC ENGINEER NEEDS MRS],'factVessels'[Date] = DATE(2023,01,01))

[Steph83]

It works, thanks!