Calculating change in data between two report dates within the same sheet

[jh292]

Hi

New to powerBI, hopefully a quick fix. 

I have a table that I need to make a messure for (I think) to show me the change over a period of time. Ideally that I can adapt / copy paste so I can create messures for 7 days 30 days 90 days etc. 

The table is created using Combined Files from reports loaded ad-hocly (sometimes daily some times weekly)

The table shows data that my team have captured from our customers. It calculates the number of insight items they have collected and returns a total "Known Insight" value for each customer.

I want to be able to show the change over a given period of time, ideally dynamically so I dont have to do anything exect keep refreshing reports into the power BI and topping up the sheet!

Here is my table. 

Source.Name	Name	Known Insight	Report Date
Site - Insight 07.10.2020.csv	Customer A	4	07-Oct-20
Site - Insight 07.10.2020.csv	Customer B	4	07-Oct-20
Site - Insight 07.10.2020.csv	Customer C	4	07-Oct-20
Site - Insight 05.10.2020.csv	Customer A	4	05-Oct-20
Site - Insight 05.10.2020.csv	Customer B	3	05-Oct-20
Site - Insight 05.10.2020.csv	Customer C	2	05-Oct-20
Site - Insight 27.9.2020.csv	Customer A	1	27-Sep-20
Site - Insight 27.9.2020.csv	Customer B	1	27-Sep-20
Site - Insight 27.9.2020.csv	Customer C	1	27-Sep-20

If I had a message to calculate insight gathered in the last 2 days the answer would be 3 (none from customer a, 1 from customer b and 2 from customer c)

The dates that the reports are sourced will change so I believe the messure is something to do with the total from the latest report (within the last 2 days) minus the next report after the date range (2days). that way if I havent run a report for two days the answer is 0. But IF I run a messure for 90 days I want the lastest report (within 90days) minus the next report out side of the 90 days (possible 92 days for example) 

The Report Date field has a relationship to a date table shown below (starts at 2017 but runs through till well have 2025;

Date

Year

QuarterOfYear

MonthOfYear

DayOfMonth

Month Name

Month & Year

QuarterInCalendar

DayInWeek

DayOfWeekName

WeekEnding

Week Number

Month Year Sort

Qrt in Year Sort

ShortYear

FY

Quarters

Sales Year Quarter

Sales Quarter & Year

03 July 2017

2017

3

7

3

July

Jul 2017

Q3 2017

0

Monday

########

28

20170700

20170300

17

FY18

Q3

Q2

Q2 2018

04 July 2017

2017

3

7

4

July

Jul 2017

Q3 2017

1

Tuesday

########

28

20170700

20170300

17

FY18

Q3

Q2

Q2 2018

Oh my! help please!

Many thanks

James

[amitchandak]

@jh292 , Not very clear, but with date table, you can try

Rolling 30= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],staroffmonth(Sales[Sales Date]),-30,day))

Rolling 30 to 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-30,day)),-30,day))

Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],staroffmonth(Sales[Sales Date]),-3,day))

Rolling 3 to 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-3,day)),-3,day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[jh292]

Affraid that still just totals everything regardless of date.... 

Thanks for trying!

[Anonymous · Accepted solution]

Hi @jh292 

I build a table like yours to have a test.

To calculate insight gathered in the last 2 days, firstly calculate the Maxdate1(2020/10/13) within last 2 days and then calculate the Maxdate2 which is before Maxdate1(2020/10/11).

Measure:

Measure = 
VAR _Max2Days =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            DATEDIFF ( 'Table'[Report Date], TODAY (), DAY ) <= 2
        ),
        'Table'[Report Date]
    )
VAR _MaxMinus =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Report Date] < _Max2Days ),
        'Table'[Report Date]
    )
VAR _SumA =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _Max2Days ),
        'Table'[Known Insight]
    )
VAR _SumB =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _MaxMinus ),
        'Table'[Known Insight]
    )
RETURN
    _SumA - _SumB

 Result is A: 6-5=1,B:6-6=0,C:5-4=1 sum=2.

You can download the pbix file from this link: Calculating change in data between two report dates within the same sheet

Best Regards,

Rico Zhou

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