Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure for previous available date

I'm currently using the 'card with states by okviz' custom visual. They have a target field which is used to compare against the current measure.   I'm looking to compare against the previous avail...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous,

    You can create a measure as below:

    Pre FileCount =
VAR _curfolderid =
    MAX ( 'Good Data'[Folder ID] )
VAR _curdate =
    MAX ( 'Good Data'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Good Data'[Date] ),
        FILTER (
            ALL ( 'Good Data' ),
            'Good Data'[Folder ID] = _curfolderid
                && 'Good Data'[Date] < _curdate
        )
    )
VAR _prefilecount =
    CALCULATE (
        SUM ( 'Good Data'[File Count] ),
        FILTER (
            ALL ( 'Good Data' ),
            'Good Data'[Folder ID] = _curfolderid
                && 'Good Data'[Date] = _predate
        )
    )
RETURN
    _prefilecount​

    Best Regards

    Rena

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous ,

As measure with date table

example

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

 

if you only need date

MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])

 

In this blog, I have dicussed column way too

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak 

    I do not fully understand your solution. 

    Your first example returns (blank) for all dates when I tried it with my data. Can you have another look at my question and if needed ask for clarification. Sorry if my question isn't clear enough.

    using:

    Last Day Non Continuous = CALCULATE(SUM('Test Data'[File Count]),filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous,

      You can create a measure as below:

      Pre FileCount =
VAR _curfolderid =
    MAX ( 'Good Data'[Folder ID] )
VAR _curdate =
    MAX ( 'Good Data'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Good Data'[Date] ),
        FILTER (
            ALL ( 'Good Data' ),
            'Good Data'[Folder ID] = _curfolderid
                && 'Good Data'[Date] < _curdate
        )
    )
VAR _prefilecount =
    CALCULATE (
        SUM ( 'Good Data'[File Count] ),
        FILTER (
            ALL ( 'Good Data' ),
            'Good Data'[Folder ID] = _curfolderid
                && 'Good Data'[Date] = _predate
        )
    )
RETURN
    _prefilecount​

      Best Regards

      Rena