Forum Discussion

Anmolgan's avatar
Anmolgan
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

How to right dynamic DAX queries?

I have a few dates for a specific company below is an example:

 

CompanyName         Effective Date    Rates

FH1                            2016-07-01       230

FH1                            2016-12-01       110

  

I want to calculate the end date as of above dates so I have a logic in mind for the first row the end date will be -1 the next rows effective date. that is 2016-10-30, and for second row the end date will be the current date for now, and the data for second row will change whenver there will be a third row, hence let say if I have a third row for the same company and dated as 2017-12-01, then for second row the end date will change to be 2017-11-30, and for the third row end date will remain as the current date. 

 

We need to find all the dates in between the effective date and the end date so that we can run the powerbi reports for those specific dates and get the desired rates for those dates.

 

I need to right a dax which perform this operation for each of the companies, And I have 145 different companies that contains different effective dates.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anmolgan ,

    You can try to use following measure formula to find out next date-1 based on current company and effective date:

    Measure =
VAR currDate =
    MAX ( Table[Effective Date] )
VAR endDate =
    CALCULATE (
        MIN ( Table[Effective Date] ),
        FILTER ( ALLSELECTED ( Table ), [Effective Date] > currDate ),
        VALUES ( Table[CompanyName] )
    )
RETURN
    IF ( endDate <> BLANK (), endDate - 1 )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anmolgan ,

    You can try to use following measure formula to find out next date-1 based on current company and effective date:

    Measure =
VAR currDate =
    MAX ( Table[Effective Date] )
VAR endDate =
    CALCULATE (
        MIN ( Table[Effective Date] ),
        FILTER ( ALLSELECTED ( Table ), [Effective Date] > currDate ),
        VALUES ( Table[CompanyName] )
    )
RETURN
    IF ( endDate <> BLANK (), endDate - 1 )

    Regards,

    Xiaoxin Sheng

    • Anmolgan's avatar
      Anmolgan
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      Anonymous 

      Hi thanks for the answer, do you know what needs to be done if I want to find the dates between start and end date? I want to have certain date periods between those start and end date ranges, so that I can plot that specifc date into powerbi and can find results for a specific month?