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 available date as my data is not continuous.

Folder IDFile CountDatePreDate
1007Sept 22, 2020Sept 15, 2020
1003Sept 15, 2020Sept 8, 2020
1003Sept 8, 2020(blank)
10120Oct 2, 2020Sept 22, 2020
10115Sept 22, 2020Sept 15, 2020
10115Sept 15, 2020 Sept 15, 2020 
10110Sept 8, 2020 (blank)

For many folders

Note: Folder 100 was deleted on Sept 23 so it no longer appears in the data

 

So far I've used this formula to create a column determining the previous file count.

Pre FileCount = LOOKUPVALUE('Good Data'[File Count],'Good Data'[Autodesk Folder ID],'Good Data'[Autodesk Folder ID],'Good Data'[date],'Good Data'[predate])
However, this does not take into consideration deleted folders.
 
Goal:
A measure that returns the sum of the previous period. OR
A way to create rows for deleted folders. 
Folder IDFile CountDatePreDatePre FileCount
1000Oct 2, 2020Sept 22, 20207
1007Sept 22, 2020Sept 15, 20203
1003Sept 15, 2020Sept 8, 20203
1003Sept 8, 2020(blank)(blank)
10120Oct 2, 2020Sept 22, 202015
10115Sept 22, 2020Sept 15, 202015
10115Sept 15, 2020 Sept 15, 2020 10
10110Sept 8, 2020 (blank)(blank)

Thank you,

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

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I’d use Power Query: Assuming you are gonna sort this thing;

    Add a custom column like so: let L => each List.PositionOf([Count])-1 in 

     [Count]-List.Range([Count], L)