cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjschminke Frequent Visitor
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 3114056.36
September 114056.36
September 214056.36
September 314056.36
September 414056.36
September 514056.36
September 614056.36
September 714056.36
September 814056.36
September 914056.36
September 1014056.36
September 1114056.36
September 1214056.36
September 1314056.36
September 1442169.08
September 1542169.08
September 1642169.08
September 1742169.08
September 1842169.08
September 1942169.08
September 2042169.08

 

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:

Report.JPG

 

 

 

 

 

 

 

 

6 REPLIES 6
Community Support Team
Community Support Team

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

Hi @sjschminke,

 

I have a little confused about your scenario.

 

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.

 

Capture.PNG

Here is my test output.

result output.PNG

 

 

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.
sjschminke Frequent Visitor
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

ProductDateAmount
A8/31/201814056.36
A9/1/201814056.36
A9/2/201814056.36
A9/3/201814056.36
A9/4/201814056.36
A9/5/201814056.36
A9/6/201814056.36
A9/7/201814056.36
A9/8/201814056.36
A9/9/201814056.36
A9/10/201814056.36
A9/11/201814056.36
A9/12/201814056.36
A9/13/201814056.36
A9/14/201842169.08
A9/15/201842169.08
A9/16/201842169.08
A9/17/201842169.08
A9/18/201842169.08
A9/19/201842169.08
A9/20/201842169.08
B9/5/20185018.11
B9/6/20185018.11
B9/7/20185018.11
B9/8/20185018.11
B9/9/20185018.11
B9/10/20185018.11
B9/11/20185018.11
B9/12/20182509.06
B9/13/20182509.06
B9/14/20182509.06
B9/15/20182509.06
C9/21/2018502.06
C9/22/2018502.06
C9/23/2018502.06
C9/24/2018502.06
C9/25/2018502.06
C9/26/2018502.06
C9/27/2018502.06
C9/28/2018502.06
C9/29/2018502.06
C9/30/2018502.06
C10/1/2018502.06
C10/2/2018502.06
C10/3/2018502.06
C10/4/2018502.06
C10/5/2018502.06
C10/6/2018502.06

 

 

 

 

 

sjschminke Frequent Visitor
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:

 

ProductAmount StartAmount End
A14056.360
B00
C0502.06

 

What I am getting is this:

 

ProductAmount StartAmount End
A14056.3642169.08
B5018.112509.06
C502.06502.06
Community Support Team
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.

Capture.PNG

 

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.
sjschminke Frequent Visitor
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)?

Highlighted
sjschminke Frequent Visitor
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.

 

Capture.JPG

 

Capture.JPGCapture.JPG