Infinity Value due to no sales in previous date (holiday or weekend)

[Anonymous]

Hi,

How do I write a code that will choose the previous day that have sales value?

Currently I am using the formula as shown below with the "KPI" visual in Power BI to do it but when the previous day are holiday or weekends (which the shop is close) the comparison changes will show infinity.

Sales(Today) = calculate(sum([sales]),lastdate([dates]))

Sales(t-1)= calculate(sum([sales]),previousday(lastdate([dates])))

Sales(MTD) = TotalMTD(sum([Sales]),[date])

Sales(M-1TD) = calculate(sum([Sales]),DateMTD(DateAdd([date],-1,month)))

In the "KPI" visual, I will set the goal/target as Sales(t-1) or Sales(M-1TD) as base of comparison.

And how can I do it for MTD vs previous month MTD, and YTD vs previous year YTD?

Please help,

[v-haibl-msft]

@Anonymous

If you have a holiday table like below, we can create a calendar table which only includes working days with following formula. Then add Year and Month column for it.

Date_WorkDay = 
CALCULATETABLE (
    Dates,
    FILTER (
        Dates,
        WEEKDAY ( Dates[Dates] ) <> 1
            && WEEKDAY ( Dates[Dates] ) <> 7
            && RELATED ( Holiday[Holiday] ) = BLANK ()
    )
)

Year = YEAR( Date_WorkDay[Dates] )

Month = MONTH( Date_WorkDay[Dates] )

 

The next step is to create the measures you wanted.

Sales(Today) = CALCULATE ( SUM ( Table1[Sales] ), LASTDATE ( Date_WorkDay[Dates] ) )

Sales(t-1)_WorkDay = 
VAR LastWorkDay =
    CALCULATE ( MAX ( Date_WorkDay[Dates] ), Date_WorkDay[Dates] < TODAY () )
RETURN
( CALCULATE ( SUM ( Table1[Sales] ), Date_WorkDay[Dates] = LastWorkDay ) )

Sales(MTD) = TOTALMTD ( SUM ( Table1[Sales] ), Date_WorkDay[Dates] )

Sales(M-1TD) = CALCULATE ( SUM ( Table1[Sales] ), PARALLELPERIOD ( Date_WorkDay[Dates], -1, MONTH ) )

Sales(YTD) = TOTALYTD ( SUM ( Table1[Sales] ), Date_WorkDay[Dates] )

Sales(Y-1TD) = CALCULATE ( SUM ( Table1[Sales] ), PARALLELPERIOD ( Date_WorkDay[Dates], -1, YEAR ) )

Best Regards,

Herbert

[Anonymous]

Hi Herbert, thanks for replying. How about if I have a table with only working dates. As I have external data as well which will be input into an excel spreadsheet manually everyday.

[v-haibl-msft]

@Anonymous

So there is only working dates in the sales table (Table1)? For example, if 7/21/2016 is not working day, then it will not exist in Table1 as below.

If so, you can use below formula for Sales(t-1).

Sales(t-1) =
VAR LastWorkDay =
    CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < TODAY () )
RETURN
( CALCULATE ( SUM ( Table1[Sales] ), Dates[Date] = LastWorkDay ) )

Best Regards,

Herbert

[Anonymous]

Hi Herbert, I am sorry for the late reply. By using the function "Today()" it will fix the sales amount to today. It disable visual to visual interaction. How can I solve this issue?

[v-haibl-msft]

@Anonymous

I’m not so clear about “It disable visual to visual interaction”. Could you please describe it more clearly?

BTW, you can try with following measure without using Today().

Sales(t-1) = 
VAR LastDay =
    MAX ( Table1[Date] )
VAR LastWorkDay =
    CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < LastDay )
RETURN
    ( CALCULATE ( SUM ( Table1[Sales] ), Table1[Date] = LastWorkDay ) )

Best Regards,

Herbert

[Anonymous]

Hi Herbert,

I tried your method, but when I tried to insert the measure into a table (like a pivot table), it returns error. Is there a way to enable it?

I am sorry for the late reply, been very busy.

[v-haibl-msft]

@Anonymous

I used this measure in KPI visual as you mentioned before. Which columns do you have in your table and what is the error message returned?

Best Regards,

Herbert