Measure that uses selected time range in its calculation

[Anonymous]

Hello,

I want to try out a new ratio for my area (Number of A x Months of the period/...) and I was wondering if it is possible. My idea is that the time would be selected here:

And then the number of months in the time range selected would go into the measure, this is a ratio that's going to go from 0 to 1 and get a color depending on the ratio value, but it has to be adjusted to the time frame.

Any ideas on how to get it done or what is my best option?

TIA

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

You can try this measure to calculate the percentage of days you selected:

Percentage =
var _datediff=DATEDIFF(MINX(ALL('Calendar'),'Calendar'[Date]),MAXX(ALL('Calendar'),'Calendar'[Date]),MONTH)

var _SelectedDateDiff=DATEDIFF(MINX(ALLSELECTED('Calendar'),'Calendar'[Date]),MAXX(ALLSELECTED('Calendar'),'Calendar'[Date]),MONTH)

Var _percentage=DIVIDE(_SelectedDateDiff,_datediff)

return _percentage

If this measure doesn’t meet your requirement, maybe you can show me your expected result and some sample data(without sensitive data).

And if you want the background color of this measure data changes depending on its value(from 0 to 1), you can follow my steps:

1. create a table visual, and add this measure to the value field, then create a slicer, add [date] column to the value field.
2. click conditional formatting, like this:

3. change the setting, like this:

4. And click OK, you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

You can get date diff like

Measure =
var _min = minx(Allselected(Date), Date[Date])
var _max = maxx(Allselected(Date), Date[Date])
return
datediff(_min, _max , Month())

You can use Day, Qtr, year diff

For color measure example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

You can use these in coditional formatting using "Field Value" Option

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values