Filter date ranges based on text filter value

[TDisco]

Having an issue building a Filter dropdown with ‘names’ that aggregate dates. I get a feeling it’s a Power BI concept I’m not grasping. Have search many solutions for many days. This is what I currently have:

A separate table with the following “Selection” values (with their intent):

• Current Reporting Month (The previous month)
• Previous Month (2 months ago)
• Last Three months (not including current month)

The Column used in a table for this Filter:

mm_slicer =

IF(HASONEFILTER(Date_Slicer[Selection]),

    SWITCH(SELECTEDVALUE(Date_Slicer[Selection]),

        "Current Reporting Month", tbl_MetricsMerge[mm_sum_UnplannedMinutes_current],

        "Previous Month", tbl_MetricsMerge[mm_sum_UnplannedMinutes_previous],

        "Last 3 Months", tbl_MetricsMerge[mm_sum_UnplannedMinutes_last3]

    ),

   tbl_MetricsMerge[mm_sum_UnplannedMinutes_current]

)

DAX for the three values mentioned above:

• mm_sum_UnplannedMinutes_current = CALCULATE(SUM('tbl_MetricsMerge'[MetricResult]),'tbl_MetricsMerge'[Metric] = "Unplanned Downtime Minutes" , DATEDIFF (tbl_MetricsMerge[Date], TODAY (), MONTH) = 1)
• mm_sum_UnplannedMinutes_previous = CALCULATE(SUM('tbl_MetricsMerge'[MetricResult]),'tbl_MetricsMerge'[Metric] = "Unplanned Downtime Minutes" , DATEDIFF (tbl_MetricsMerge[Date], TODAY (), MONTH) = 2)
• mm_sum_UnplannedMinutes_last3 = CALCULATE(SUM('tbl_MetricsMerge'[MetricResult]),'tbl_MetricsMerge'[Metric] = "Unplanned Downtime Minutes", DATEDIFF (tbl_MetricsMerge[Date], TODAY (), MONTH) <= 3)

I can load these measures individually into a table, and see the values as needed. Wonderful – thumbs up.

HOWEVER, when using the mm_slicer measure in a table that applies the Filter, the first “current” and “previous” work, but the “last3” does not, it will only display the third month. How can I get a rolling value of 3 months?

Issues to be resolved:

• Calculate a 3-month rolling sum (Previous month plus 2 more)
• Filter to count only a specific type of value (Unplanned Downtime Minutes)

PS: The tbl_MetricsMerge dataset contains the first day of every month for 2022 (which is used as a placeholder in the data entry form this data is attached to.)

PSS: I have a "Date" table.

[TDisco · Accepted solution]

I had a relationship link from the Date Selector to the Date Table. Removing that made all this work.

[v-yalanwu-msft]

Hi, @TDisco ;

Please refer to this link.

https://community.powerbi.com/t5/Desktop/Calculating-the-average-in-the-graphs/td-p/2277452

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team_ Yalan Wu

[v-yalanwu-msft]

Hi, @TDisco ;

I tested it , It's all true in the cases I tested. Maybe you missed something that we didn't notice. For example, your table has other filters, and secondly, you can compare your [MM_slicer] and [mm_sum_UnplannedMinutes_last3] in a table. The external conditions all reduce the variables.

Or you could share more detail about your  structure and relationships of the data, or a simple file？Looking forward your reply!
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TDisco]

Lets try one more screen shot. Here is the file Google Drive (temp link)

And hey. thanks for sticking with me on this 😃

See image. No filters are present, except for what is in the measures.

1. Filter is linked to Table (only this table)
2. The measure to build the links and the measures placed in a table.
   • I added a test_3rdmonth measure to validate the _last3 measure
3. All the measures are written like this.  The _last3 should equal the sum of these three measures: _current, _previous, _test_3rdmonth - which it does in the "2" table - but when used with the filter in table "1" - only returns the 3rd month value and not the last 3 month sum.

Dataset. is trimmed for just two systems.

• Column – Data Type
• System is Text
• Metric is Text
• Date is Date (All Dates are for start of every month = January 2021 – 1/1/2021)
• MetricResult is General (Not Summarized)

[v-yalanwu-msft]

Hi, @TDisco ;

I can't really judge from the screenshot you gave, your link needs permission to open.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TDisco]

Do you recommend another method to share the file?

[TDisco]

A simpler recap. I have a filter that has words. I have a measure column, that based on the word selcted, runs one of 3 measures. The measures contain a formula that matches the a SUM value of the word. All these measures return the correct values (I can prove that by putting in a stand alone table) While I can get Current and Previous month to return a correct value, I'm not able to return a rolling 3-month count. (this DAX works and is proven in a table).... Any reason it would work in a table, but not in filtered measure? - Thanks (edits - clarity)

[amitchandak]

@TDisco , if there is no interaction between visuals, then it should take default value and show value for that