Forum Discussion

paulineCom's avatar
paulineCom
Icon for Helper I rankHelper I
5 years ago
Solved

DAX Calculate funciton doesn't filter

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.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      paulineCom
      Icon for Helper I rankHelper I
      5 years ago

      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.
    • paulineCom's avatar
      paulineCom
      Icon for Helper I rankHelper I
      5 years ago

      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 ?

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      paulineCom
      Icon for Helper I rankHelper I
      5 years ago

      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.