Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Automatically fixing a date in measure?

Hi,

 

I had a rather lengthy post yesterday that for some reason disappeared and which I do intend to repost, but I had a question regarding using dates within a filter.  Consider the following measure:

 

DQ 2019 Benchmark = CALCULATE([DQ Rate 2PY (Forecast)],
                                                          STARTOFMONTH(dimDate[Date]))
                                   - CALCULATE([dq_ar_switch] / [$ CY AR],
                                                         STARTOFMONTH(dimDate[Date]))
 
The STARTOFMONTH function fixes the date at the beginning of the month for whichever date range is shown on the chart.  But what if I add a date slider and begin my chart at, say, the 4th or 5th of the month instead of the 1st?  Is there anyway that I can create a filter which will automatically detect the minimum possible date in the chart, and have that date be fixed similar to using something like the STARTOFMONTH function above?
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Min date in selection /visual

    DQ 2019 Benchmark =
    var _min = minx(allselected(dimDate), dimDate[Date])
    return
    CALCULATE([DQ Rate 2PY (Forecast)],filter(dimDate,dimDate[Date] = _min )) - CALCULATE(divide([dq_ar_switch] , [$ CY AR]),filter(dimDate,dimDate[Date] = _min ))

    or

     

    First date each month

    DQ 2019 Benchmark =

    CALCULATE([DQ Rate 2PY (Forecast)],filter(dimDate,dimDate[Date] = eomonth( dimDate[Date],-1)+1 )) - CALCULATE(divide([dq_ar_switch] , [$ CY AR]),filter(dimDate,dimDate[Date] = eomonth( dimDate[Date],-1)+1 ))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Min date in selection /visual

    DQ 2019 Benchmark =
    var _min = minx(allselected(dimDate), dimDate[Date])
    return
    CALCULATE([DQ Rate 2PY (Forecast)],filter(dimDate,dimDate[Date] = _min )) - CALCULATE(divide([dq_ar_switch] , [$ CY AR]),filter(dimDate,dimDate[Date] = _min ))

    or

     

    First date each month

    DQ 2019 Benchmark =

    CALCULATE([DQ Rate 2PY (Forecast)],filter(dimDate,dimDate[Date] = eomonth( dimDate[Date],-1)+1 )) - CALCULATE(divide([dq_ar_switch] , [$ CY AR]),filter(dimDate,dimDate[Date] = eomonth( dimDate[Date],-1)+1 ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you!  Using this portion solved my issue completely:

       

      var _min = minx(allselected(dimDate), dimDate[Date])