cancel
Showing results for
Did you mean:
Frequent Visitor

## Compare values of a measure using date slicer as variable inputs

Hi,

I am trying to compare the value of a measure on 2 specific dates using a date slicer as variable inputs.  For example, I want to know the difference in value from August 31 to September 30.  I select these dates in the slicer.  I then create two measures that look like this:

Amount Start = VAR startdate = MIN('Backorder Query 1'[Date])
RETURN CALCULATE(SUM('Backorder Query 1'[Amount]),FILTER('Backorder Query 1','Backorder Query 1'[Date]=startdate))

Amount End = VAR enddate = MAX('Backorder Query 1'[Date])
RETURN CALCULATE(SUM('Backorder Query 1'[Amount]),FILTER('Backorder Query 1','Backorder Query 1'[Date]=enddate))

This works great at an aggregated level, but when I drop in my product hierarchy and view these measures in a matrix visual, it does not give me the value I need at the product level.

For example, one product has values like this (within the date range):

 August 31 14056.4 September 1 14056.4 September 2 14056.4 September 3 14056.4 September 4 14056.4 September 5 14056.4 September 6 14056.4 September 7 14056.4 September 8 14056.4 September 9 14056.4 September 10 14056.4 September 11 14056.4 September 12 14056.4 September 13 14056.4 September 14 42169.1 September 15 42169.1 September 16 42169.1 September 17 42169.1 September 18 42169.1 September 19 42169.1 September 20 42169.1

September 21st - September 30th are all 0, and therefore the records do not exist in the data set.

The value returned for [Amount End] in this case is 42,169.08 because it is the value at the MAX date within this range.  But what I really need is for it to return 0 on September 30th.

Any tips would be greatly appreciated!

Here is what the report looks like:

6 REPLIES 6
Community Support Team

## Re: Compare values of a measure using date slicer as variable inputs

Hi @sjschminke,

It seems that you don't have another date table.

Do you have a complete date column in Backorder Query 1 table?

From your data sample, there is only have the date from August 31 to Sep 20, but your slicer have the  between date slicer which the max date is Sep 30.

By my tests, if I have a data sample like below and create your measures, I could see the Amount end return 0.

Here is my test output.

Best  Regards,
Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Compare values of a measure using date slicer as variable inputs

Hi Cherry,

Yes, I have a separate date table (not being used in this case), but I don't believe that is the problem here.  Below is some additional data that might help clarify.  My problem is that the records in your test sample from 9/21 - 9/30 don't exist for "Product A".  If the product is not backordered, I don't have a record that says (product) A, (date) 9/21/2018, (Amount) 0.

Table: Backorder Query 1

 Product Date Amount A 8/31/2018 14056.36 A 9/1/2018 14056.36 A 9/2/2018 14056.36 A 9/3/2018 14056.36 A 9/4/2018 14056.36 A 9/5/2018 14056.36 A 9/6/2018 14056.36 A 9/7/2018 14056.36 A 9/8/2018 14056.36 A 9/9/2018 14056.36 A 9/10/2018 14056.36 A 9/11/2018 14056.36 A 9/12/2018 14056.36 A 9/13/2018 14056.36 A 9/14/2018 42169.08 A 9/15/2018 42169.08 A 9/16/2018 42169.08 A 9/17/2018 42169.08 A 9/18/2018 42169.08 A 9/19/2018 42169.08 A 9/20/2018 42169.08 B 9/5/2018 5018.11 B 9/6/2018 5018.11 B 9/7/2018 5018.11 B 9/8/2018 5018.11 B 9/9/2018 5018.11 B 9/10/2018 5018.11 B 9/11/2018 5018.11 B 9/12/2018 2509.06 B 9/13/2018 2509.06 B 9/14/2018 2509.06 B 9/15/2018 2509.06 C 9/21/2018 502.06 C 9/22/2018 502.06 C 9/23/2018 502.06 C 9/24/2018 502.06 C 9/25/2018 502.06 C 9/26/2018 502.06 C 9/27/2018 502.06 C 9/28/2018 502.06 C 9/29/2018 502.06 C 9/30/2018 502.06 C 10/1/2018 502.06 C 10/2/2018 502.06 C 10/3/2018 502.06 C 10/4/2018 502.06 C 10/5/2018 502.06 C 10/6/2018 502.06

Frequent Visitor

## Re: Compare values of a measure using date slicer as variable inputs

What I would expect if I filter on 8/31 & 9/30 would be:

 Product Amount Start Amount End A 14056.36 0 B 0 0 C 0 502.06

What I am getting is this:

 Product Amount Start Amount End A 14056.36 42169.08 B 5018.11 2509.06 C 502.06 502.06
Community Support Team

## Re: Compare values of a measure using date slicer as variable inputs

Hi @sjschminke,

You could change your formulas like below, then you will get your desired output.

Amount End =
VAR enddate =
ENDOFMONTH ( 'Backorder Query 1'[Date] )
RETURN
IF (
CALCULATE (
SUM ( 'Backorder Query 1'[Amount] ),
FILTER ( 'Backorder Query 1', 'Backorder Query 1'[Date] = enddate )
)
= BLANK (),
0,
CALCULATE (
SUM ( 'Backorder Query 1'[Amount] ),
FILTER ( 'Backorder Query 1', 'Backorder Query 1'[Date] = enddate )
)
)

Amount Start =
VAR startdate =
STARTOFMONTH ( 'Backorder Query 1'[Date] )
RETURN
IF (
CALCULATE (
SUM ( 'Backorder Query 1'[Amount] ),
FILTER ( 'Backorder Query 1', 'Backorder Query 1'[Date] = startdate )
)
= BLANK (),
0,
CALCULATE (
SUM ( 'Backorder Query 1'[Amount] ),
FILTER ( 'Backorder Query 1', 'Backorder Query 1'[Date] = startdate )
)
)

Then you could get the output like below.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Compare values of a measure using date slicer as variable inputs

This does not provide the flexibility I'm ultimately looking for.  What if I want to compare September 5th to September 12th (by adjusting the slicer)?

Frequent Visitor

## Re: Compare values of a measure using date slicer as variable inputs

Hi Cherry,

Will there ever be the possibility to choose data type "date" as a what-if parameter?  I can sort of get to the same desired result with a series of "whole number" parameters and custom measures.  But it would be much nicer if the Power BI team would add date as a possible data type.

Example:

Amount Start = SUMX('Backorder Query 1',IF(MONTH('Backorder Query 1'[Date])='Month Start'[Month Start Value],IF(DAY('Backorder Query 1'[Date])='Day Start'[Day Start Value],'Backorder Query 1'[Amount],0),0))

Amount End = SUMX('Backorder Query 1',IF(MONTH('Backorder Query 1'[Date])='Month End'[Month End Value],IF(DAY('Backorder Query 1'[Date])='Day End'[Day End Value],'Backorder Query 1'[Amount],0),0))

Where [Month Start Value], [Day Start Value], [Month End Value], [Day End Value], are all whole number parameters.