Creating Dynamic Date Based on the Date Range

Question

Dear Power BI Experts&nbsp;&nbsp;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&nbsp;In-ICU Mortality Rate based on the Date,Type,Loc,DepartmentSlices -Date and Type comes from Dynamic Scope Table (which i have joined to my Orginal Data)Dynamic Date Scope = UNION(&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),&nbsp; &nbsp; &nbsp; &nbsp; "VisualDate", DATE(YEAR([Date]), MONTH([Date]), 1),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Monthly",&nbsp; &nbsp; &nbsp; &nbsp; "Pulse", 1,&nbsp; &nbsp; &nbsp; &nbsp; "Calendar Year", "CY " &amp; YEAR([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Month Name", FORMAT([Date], "mmmm"),&nbsp; &nbsp; &nbsp; &nbsp; "Month Number", MONTH([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday", FORMAT([Date], "dddd"),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday number", WEEKDAY([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Quarter", "Q" &amp; TRUNC((MONTH([Date]) - 1) / 3) + 1&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),&nbsp; &nbsp; &nbsp; &nbsp; "VisualDate", DATE(YEAR([Date]), 3 * (QUARTER([Date]) - 1) + 1, 1),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Quarterly",&nbsp; &nbsp; &nbsp; &nbsp; "Pulse", 2,&nbsp; &nbsp; &nbsp; &nbsp; "Calendar Year", "CY " &amp; YEAR([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Month Name", FORMAT([Date], "mmmm"),&nbsp; &nbsp; &nbsp; &nbsp; "Month Number", MONTH([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday", FORMAT([Date], "dddd"),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday number", WEEKDAY([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Quarter", "Q" &amp; TRUNC((MONTH([Date]) - 1) / 3) + 1&nbsp; &nbsp; ),&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),&nbsp; &nbsp; &nbsp; &nbsp; "VisualDate", DATE(YEAR([Date]), 1, 1),&nbsp; &nbsp; &nbsp; &nbsp; "Type", "Yearly",&nbsp; &nbsp; &nbsp; &nbsp; "Pulse", 3,&nbsp; &nbsp; &nbsp; &nbsp; "Calendar Year", "CY " &amp; YEAR([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Month Name", FORMAT([Date], "mmmm"),&nbsp; &nbsp; &nbsp; &nbsp; "Month Number", MONTH([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday", FORMAT([Date], "dddd"),&nbsp; &nbsp; &nbsp; &nbsp; "Weekday number", WEEKDAY([Date]),&nbsp; &nbsp; &nbsp; &nbsp; "Quarter", "Q" &amp; TRUNC((MONTH([Date]) - 1) / 3) + 1&nbsp; &nbsp; ))&nbsp;In-ICU Mortality Rate Numerator = VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")VAR StartDate = [StartDate]VAR EndDate = [EndDate]RETURN&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; SelectedType,&nbsp; &nbsp; &nbsp; &nbsp; "Monthly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[EXP_IN_ICU_BOOL] = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Quarterly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[EXP_IN_ICU_BOOL] = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Yearly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[EXP_IN_ICU_BOOL] = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )In-ICU Mortality Rate Denominator = VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")VAR StartDate = [StartDate]VAR EndDate = [EndDate]RETURN&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; SelectedType,&nbsp; &nbsp; &nbsp; &nbsp; "Monthly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Quarterly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Yearly", CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('The Pulse Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &gt;= StartDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'The Pulse Data'[ICU_STAY_END_DTTM] &lt;= EndDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )In-ICU Mortality Rate = DIVIDE([In-ICU Mortality Rate Numerator], [In-ICU Mortality Rate Denominator], 0)&nbsp;These are my DAX.&nbsp;&nbsp;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.

vinutha · Answer

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

anonymous · Answer

Hi&nbsp;Vinutha&nbsp;,
&nbsp;
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
--&nbsp;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.
&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Creating Dynamic Date Based on the Date Range

2 Replies

Related content

dynamic x-axis based on filter

Creating a dynamic slicer

Create Dynamic Backlog Dates Backwards based on week

Dynamic images based on selections

create dynamic axis based on two slicers