DAX Calculate funciton doesn't filter

[paulineCom]

Hello,

I need help on this one, i have a XVENTES table (in direct query) witch contains a date (DateEve) and a ValEur column. I want to compare results of the previous month and the actual one, I've already created the mesure that sum the ValEur for the curent month.

now i need to take the sum of ValEur in a period time that starts with a mesure and ends with another one.

Below you have my dax syntax for my CA month-1, and it's apparenlty ignoring my filter based on the two dates.

The syntax of the two mesures returning my dates limits are below,

CA Month-1 =
CALCULATE( sum(XVENTES[ValEur]), 
                                     DATESBETWEEN(XVENTES[DateEve],
                                                         [Date_month_str-1],
                                                         [Date_month_end-1]))

//end date (it's another mesure)
Date_month_end-1 = 
DATE(YEAR(LASTDATE(XVENTES[DateEve])),
          MONTH(LASTDATE(XVENTES[DateEve]))-1,
          DAY(LASTDATE(XVENTES[DateEve])))

//starting date (it's another mesure)
Date_month_str-1 = 
DATE(YEAR(LASTDATE(XVENTES[DateEve])),
          MONTH(LASTDATE(XVENTES[DateEve]))-1,
          1)

My filter is getting ignored, I don't know why

What am I doing wrong ?

Any help is appreciated. Thank you

Regards.

[V-lianl-msft · Accepted solution]

Hi @paulineCom ,

You need to create an unrelated calendar table as a slicer.

Here is the sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulineCom]

Thank you @V-lianl-msft it helped me a lot.

I'm not sure to understand how could it work with this table date unlinked to the sales table.

I've understand the dax function, it is really clear.

Sales[Date] <= MAX ( DimDate[Date] )

I ignored that would be possible. I mean, it's unlinked.

Many thanks for the example you gave me.

[amitchandak]

@paulineCom , You should date table for all time intelligence

You can get this month vs last month like these examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

//Without TI 
Month Rank = RANKX(all('Date'),'Date'[Month],,ASC,Dense)	
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions


Appreciate your Kudos.

[paulineCom]

for exemple the

 CALCULATE(SUM(XVENTES[ValEur]),DATESMTD(DimDate[Date]))

doen't return any results.

Maybe it's beacause my dimDate is not created correctly, here is the way I did it :

DimDate = ADDCOLUMNS(
    CALENDAR(DATE(YEAR(TODAY())-10,1,1),DATE(YEAR(TODAY())+1,12,31)),
    "DateEve", FORMAT([Date], "DD/MM/YYYY"),
    "Année", YEAR([Date]),
    "NoMois", MONTH([Date]),
    "Mois", FORMAT([Date], "MM"),
    "NoJour", WEEKDAY([Date]),
    "Jour", FORMAT([Date], "DD"),
    "Trimestre", FORMAT([Date], "TQ")
)

is there anything else i should to to make it work ?

[paulineCom]

Hello, thank you for helping me,

i've tried this to calculate my valEur on last month (wich is 01/08/2020 to 01/28/2020)

CA Month-1 = CALCULATE( sum(XVENTES[ValEur]), DATESMTD(ENDOFMONTH(DATEADD(XVENTES[DateEve],-1,MONTH))))

but i have some errors in my visualisation, saying : "the function DATEADD wait a contiguous selection when the date column ist'n unique, is discontinuous or contains an hour part.