Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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,

1 ACCEPTED SOLUTION

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​

Measure for previous available date.JPG

Best Regards

Rena

 

 

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

View solution in original post

4 REPLIES 4
watkinnc
Super User
Super User

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)


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
amitchandak
Super User
Super User

@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
Not applicable

@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.

mikedoesnt_0-1601937545265.png

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])))

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​

Measure for previous available date.JPG

Best Regards

Rena

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.