Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anmolgan
Post Prodigy
Post Prodigy

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.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.