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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors