How to right dynamic DAX queries?

[Anmolgan]

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.

[Anonymous · Accepted solution]

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]

@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?