Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX for Date Filter

Hi, 

I would appreciate some help to create a DAX formula that lets me pull data for the prior 12 months excluding current month until 12 days into current month (because our finance does not update full month till about 12 or 13 days into current month).

So, for example, I would be pulling June 2022-May 2023 + June 13 onwards (I know today is just June 6, but this is for future and in general).

Is that possible?
Thank you.

 

Need Help

3 Replies

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

    Anonymous , try a measure like

     

    Rolling 12 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1),-12,MONTH))

     

    Make sure you are using date table

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    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 :radacad sqlbi My Video Series Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you! I will try and let you know.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, actually, I am sorry it’s not the Dax formula I need.

      I need help with 2 things – can you please refer to the snippet of part of the Dax import query.

      For the date part of the query (which I boxed in red), I need help to modify this part of the query so that:

      1. The output shows the date in date only format, not date/time;
      2. The output is for trailing 12 months + 13 days into the 13th month, so if I were pulling it today, the output would be for Jun 2022-May 2023 (trailing 12 months) + Jun 13, 14, etc.

       

      Would you be able to help with this?

      Thanks in advance!