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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Vinutha
Frequent Visitor

Creating Dynamic Date Based on the Date Range

Dear Power BI Experts 

 

I am new to this reporting tool and i am struggling to create a Dynamic Line Chart.

So here is the context. I need to get In-ICU Mortality Rate based on the Date,Type,Loc,Department

Slices -Date and Type comes from Dynamic Scope Table (which i have joined to my Orginal Data)

Dynamic Date Scope =
UNION(
    ADDCOLUMNS(
        CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
        "VisualDate", DATE(YEAR([Date]), MONTH([Date]), 1),
        "Type", "Monthly",
        "Pulse", 1,
        "Calendar Year", "CY " & YEAR([Date]),
        "Month Name", FORMAT([Date], "mmmm"),
        "Month Number", MONTH([Date]),
        "Weekday", FORMAT([Date], "dddd"),
        "Weekday number", WEEKDAY([Date]),
        "Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
        "VisualDate", DATE(YEAR([Date]), 3 * (QUARTER([Date]) - 1) + 1, 1),
        "Type", "Quarterly",
        "Pulse", 2,
        "Calendar Year", "CY " & YEAR([Date]),
        "Month Name", FORMAT([Date], "mmmm"),
        "Month Number", MONTH([Date]),
        "Weekday", FORMAT([Date], "dddd"),
        "Weekday number", WEEKDAY([Date]),
        "Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
    ),
    ADDCOLUMNS(
        CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
        "VisualDate", DATE(YEAR([Date]), 1, 1),
        "Type", "Yearly",
        "Pulse", 3,
        "Calendar Year", "CY " & YEAR([Date]),
        "Month Name", FORMAT([Date], "mmmm"),
        "Month Number", MONTH([Date]),
        "Weekday", FORMAT([Date], "dddd"),
        "Weekday number", WEEKDAY([Date]),
        "Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
    )
)
 
In-ICU Mortality Rate Numerator =
VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
RETURN
    SWITCH(
        SelectedType,
        "Monthly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
                'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
            )
        ),
        "Quarterly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
                'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
            )
        ),
        "Yearly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
                'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
            )
        )
    )
In-ICU Mortality Rate Denominator =
VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
RETURN
    SWITCH(
        SelectedType,
        "Monthly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
            )
        ),
        "Quarterly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
            )
        ),
        "Yearly", CALCULATE(
            COUNTROWS('The Pulse Data'),
            FILTER(
                'The Pulse Data',
                YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
                'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
                'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
            )
        )
    )
In-ICU Mortality Rate =
DIVIDE([In-ICU Mortality Rate Numerator], [In-ICU Mortality Rate Denominator], 0)
 
These are my DAX. 
 
The Issue i have is on selecting Date Range,Type,Loc,Department the data is not show show as expected.
On selecting the Loc the data is completely wrong. I am not sure what i am missing at this point.
2 REPLIES 2
Vinutha
Frequent Visitor

Vinutha_0-1725450383768.pngVinutha_1-1725450426669.png

On selecting just one location the data is showing as dot though we have data.

Hi @Vinutha ,

 

Please consider providing some example data, model relationships and fields in the visual object, and it would be nice to have a pbix file available for testing (please use the example data or erase the privacy information).

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

-- On selecting just one location the data is showing as dot though we have data.

There are many possible reasons. It is possible that only one data point has data that meets the filter criteria. Or it's not expanded to a finer level, etc.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors