Forum Discussion

Charu's avatar
Charu
Icon for Post Patron rankPost Patron
8 years ago

DAX query to change the From date based on sysdate

Hi Everyone,

Have a good day :)

I come up with some logic for my date filter, please help me to write DAX formula for that.

the logic is Date Filter: Apr to June; July to Sep; Oct to Dec; Jan to March

in date filter to-date as sysdate and from date logic is here
1. if the system date is APRIL 10th then the from date should display 1st of April and our todate will be the sysdate,
2.if the sysdate is June then the from date should display from Apr 1st to till June system date.
3.Likewise, if sysdate is march then from date should display from jan1st to till system
help me how to write Dax for this logic.

 

Thanks in advance

 

Need Help

14 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Charu

     

    Does this calculated table help?

     

    It will dynamically generated rows based on the system date back to the 1st of each quarter.   I have included three columns as I'm not 100% sure what you need and they might help

     

    Table = 
VAR ToDate = TODAY()
VAR SysMonth = MONTH(ToDate)
VAR FromMonth = DATE(YEAR(ToDate), SysMonth - MOD(MONTH(ToDate)-1,3) , 1)
RETURN
    ADDCOLUMNS(
        CALENDAR( FromMonth , ToDate),
        "From Date" , FromMonth ,
        "To Date" , ToDate  )

    • Charu's avatar
      Charu
      Icon for Post Patron rankPost Patron
      8 years ago

      Hi Phil_Seamark

      Thank you so much for this very quick response.Really this formula will be helpful later

      Sorry I forget one thing to point out that is I'm Using Direct query mode and connected 5different tables each has a different query but in each table one date column will be there.

      I tried to create a custom date table in direct query mode I couldn't hope it is not possible too, so please suggest me how to make use of this formula that you have given

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    The simpliest implemenation is to have a Flag field in your data (custom column) which does a check if the date in the current row is part of the current Quarter.  This field will contain either True or False.

     

    From here, your filter can simply be to say "only show records where that field is True".  An example dax for this calculated column might be:

    isCurrentQ = IF(
	YEAR([DateField]) = YEAR(TODAY()),
	CEILING(DIVIDE(MONTH([DateField]), 3), 1) = CEILING(DIVIDE(MONTH(TODAY()), 3), 1),
	FALSE
)
    • Charu's avatar
      Charu
      Icon for Post Patron rankPost Patron
      8 years ago

      Hi Anonymous

      This to a great idea but I need to use date slicer that to BETWEEN that is From date and To-date.so that user can select the date which they want to see the data on the particular date too.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        By changing the code to this:

         

        Quarter = "Q" & CEILING(DIVIDE(MONTH([Date]), 3), 1) & "-" & YEAR([Date]) 

        You would embed the Quarter into your data instead.  This won't appear as a date slicer, but at least they will get a Quaterly picklist to choose from