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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
WEARLE
Frequent Visitor

Get data form previous day to calculate the difference

Oh hi

 

I currenly have a smart measure set that gets me data from 1/1/24 as below. I am rubbish at this so is there a way I can alter it so it just gets me the data from the previous day and calculates the difference for me? I have a day of the year column in the table if that needs to be used? 

 

difference from 01 January 2024 =
VAR __BASELINE_VALUE =
    CALCULATE(
        [Outstanding Task],
        'Daily Tasking Data'[Report Date] IN { DATE(2024, 1, 1) }
    )
VAR __MEASURE_VALUE = [Outstanding Task]
RETURN
    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
 
So need something that gives me this:
 
DayOutstanding TasksDif from prev day
123 
2654631
3123-531
454-69
5321267
63210
73215431833
8245-31909
9125-120
10545420
11125-420
1212451120
13654-591
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@WEARLE , Assume you have measure [Outstanding Task]

 

Join date of you table with a date table and create measure

 

calculate([Outstanding Task], previousday(date[Date]) )

 

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

View solution in original post

serpiva64
Super User
Super User

Hi,

you can use offset function.

create a measure

Diff using Offset =
var offsetout =CALCULATE(sum('Table'[Outstanding Tasks]), OFFSET(-1, ALLSELECTED('Table'[Day]), ORDERBY('Table'[Day], asc)))
var result = if (not ISBLANK(offsetOut), sum('Table'[Outstanding Tasks])-offsetout, BLANK())
RETURN
result
and obtain your result
serpiva64_0-1705514583543.png

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

 

View solution in original post

3 REPLIES 3
WEARLE
Frequent Visitor

Thanks @serpiva64 and @amitchandak that worked a treat 🙂

 

I have made a measure that subtracts 

 

Outstanding Task minus Dif from Previous day =
[Outstanding Task] - [Dif from Previous day]
 
How can I get it to ignore weekends? So that figure for 8/1 would use the data from 6/1 as the basline for the difference? I have a "Is Weekend" column in my date table which returns Yes No
 

WEARLE_0-1705570019541.png

 

Sorry and thanks for the help! 🙂

 

serpiva64
Super User
Super User

Hi,

you can use offset function.

create a measure

Diff using Offset =
var offsetout =CALCULATE(sum('Table'[Outstanding Tasks]), OFFSET(-1, ALLSELECTED('Table'[Day]), ORDERBY('Table'[Day], asc)))
var result = if (not ISBLANK(offsetOut), sum('Table'[Outstanding Tasks])-offsetout, BLANK())
RETURN
result
and obtain your result
serpiva64_0-1705514583543.png

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

 

amitchandak
Super User
Super User

@WEARLE , Assume you have measure [Outstanding Task]

 

Join date of you table with a date table and create measure

 

calculate([Outstanding Task], previousday(date[Date]) )

 

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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