Using the min and max from a date range

Power BI
1

Hello all,
I’m using the most current version of power bi I have an excel dataset. I need to take a date range from the user (slicer) and uses both the minimum date from that date range and the maximum from that date range. Use these two dates to pull information from a table that counts all the rows from the beginning of the table date to the minimum date range and the beginning of the table date to the maximum date range. So, if my table starts on 1/1/2020 and the user selects 1/1/2023 - 12/31/2023 I would want to calculate how many rows from 1/1/2020 to 1/1/2023 and how many rows from 1/1/2020 to 12/31/2023. My dataset has an OrderDate column (formatted 1/1/2020). I’m pretty new at Power BI so if you are going to get too deep into the woods on parameters and such, I will need a lot of detail. ha. Thanks in advance for any assistance you can provide.

2

Hello @robertdseals88 ,

Welcome to the forum.

Lets assume that we want to calculate rows and use COUNTROWS function (you can have DISTINCTCOUNT if you would like to count distinct values).

I found some sample dataset - my Dates are / Initial Set

image
image1623×724 59.7 KB

if I move the date slicer - _Test_Count before and _Test Count After will be calculated

for Example:

image
image1702×722 59.9 KB

so I used

_Test Min Selected = MINX(ALLSELECTED(dim_Calendar[Date]),dim_Calendar[Date])

What is the minimum Date of selected Date slicer ?

_Test Max Selected = MAXX(ALLSELECTED(dim_Calendar[Date]),dim_Calendar[Date])

What is the maximum?

For All Rows (despite what are you selected)

Test ALLCountRows = CALCULATE(COUNTROWS(dim_Calendar),ALL(dim_Calendar))

Simple calculate COUNTROWS for All (what you find in dimension Calendar)

For Count what is in selected period:

Test CountRows = COUNTROWS(dim_Calendar)

What is before :

Test Count Before = 
VAR _Min =[_Test Min Selected]
VAR _Result=
CALCULATE (
    [Test CountRows], 
    FILTER( ALL(dim_Calendar),
   dim_Calendar[Date] < _Min
)
)
Return _Result

Calculate COUNTROWS but you need to change context of calculation - filter so you use all calendar table (all dates) and filter where Date < Minimum Selected

For Counts After:

Test Count After = 
VAR _Max =[_Test Max Selected]
VAR _Result=
CALCULATE (
    [Test CountRows], 
    FILTER( ALL(dim_Calendar),
   dim_Calendar[Date] > _Max
)
)
Return _Result

similar syntax is use just Date > _Max is changed

Please note that
Test ALLCountRows =Test CountRows+Test Count Before +Test Count After

Hope that is what are you looking for

3

Hi @robertdseals88 - Check the Solution generated by Data Mentor for your query. Let us know if it is helpful.

Thanks
Ankit J