Show selected value with previous period value when selecting a specific date range

[Anonymous]

Hello Everyone, i have a pbix file which i will share that contains expenses table, revenues table, datetable and a sort table. https://1drv.ms/u/s!Ag9tIyk2ofNRjj9fJ_lA-8qzOsre?e=7JJxII 

i want to make a table that shows selected values of a date from a slicer and then show me the previous date next to it.

but i use a timeline slicer that has alot of options to choose from ( day,month,year,week,quarter) 

my goal is to if i select the Day granuality, and choose for example 30 august 2022, i want to see also the previous day so 29 august 2022, or if i select a range from 20 august and 30 August, i want to be able to see the values from these days and next to it yhe values previous days which is 10 days so from 9 august to 19 august.

if i choose the month granuality and select september, i want to see the previous value selected which is august.

if i choose the week granulaity, and select week 27 , i want to see the selected financial value and i want to also see previous period value which is week 26.

I would appreciate the help

[v-jianboli-msft · Accepted solution]

Hi @Anonymous 

Please try:

Create two measures to display the selected dates and previous dates:

PreviousDate = 
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
var _days = COUNTROWS(CALENDAR(_min,_Max))
return IF(_min<>_Max,"From "&_min-_days&" to "&_Max-_days,FORMAT(_min-_days,"YYYY/MM/DD"))

SelectedDate = 
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
return IF(_min<>_Max,"From "&_min&" to "&_Max,FORMAT(_min,"YYYY/MM/DD"))

Output:

Then create a new measure for previous value:

Previous Financial Value =
VAR _min =
    MIN ( 'DateTable'[Date] )
VAR _Max =
    MAX ( 'DateTable'[Date] )
VAR _days =
    COUNTROWS ( CALENDAR ( _min, _Max ) )
VAR Totalrevenue =
    CALCULATE (
        SUM ( Revenue[Amount] ),
        FILTER ( ALL ( 'Revenue' ), [Date] <= _Max - _days && [Date] >= _min - _days )
    )
VAR Payroll =
    CALCULATE (
        SUM ( Expenses[Amount] ),
        FILTER (
            ALL ( Expenses ),
            Expenses[Category] = "Payroll Expenses"
                && [Date] <= _Max - _days
                && [Date] >= _min - _days
        )
    )
VAR Business =
    CALCULATE (
        SUM ( Expenses[Amount] ),
        FILTER (
            ALL ( Expenses ),
            Expenses[Category] = "Business Expenses"
                && [Date] <= _Max - _days
                && [Date] >= _min - _days
        )
    )
VAR Financial =
    CALCULATE (
        SUM ( Expenses[Amount] ),
        FILTER (
            ALL ( Expenses ),
            Expenses[Category] = "Financial Fees"
                && [Date] <= _Max - _days
                && [Date] >= _min - _days
        )
    )
VAR Totalexpense =
    CALCULATE (
        SUM ( Expenses[Amount] ),
        FILTER ( ALL ( 'Expenses' ), [Date] <= _Max - _days && [Date] >= _min - _days )
    )
VAR Grossprofit = ( Totalrevenue - Totalexpense )
VAR Taxes = ( Totalrevenue * 0.15 )
VAR Netprofit = Grossprofit - Taxes
VAR Result =
    SWITCH (
        SELECTEDVALUE ( 'Sort Table'[Index] ),
        0, Totalrevenue,
        2, Payroll,
        4, Business,
        6, Financial,
        8, Totalexpense,
        10, Grossprofit,
        12, Taxes,
        14, Netprofit
    )
RETURN
    Result

Apply it to the table visual

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

Thnak you very much 

[amitchandak]

@Anonymous , Try a day behind sales using date table

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

or

day Behind =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()) -1
var _min = if(isfiltered('Date'),Min( 'Date'[Date]) , today()) -1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))