Current Month vs Previous Month

[Jeevan1991]

Dear Friends,

Please help me on below query in Power BI.

I have a date wise outlet sale table like the below.

Date	Outlet	Sales
30-Nov-2020	12435452	233243
29-Nov-2020	12435452	443432
30-Nov-2020	53435454	665476
29-Nov-2020	53435454	545344
28-Nov-2020	53435454	423534
30-Oct-2020	12435452	532534
29-Oct-2020	12435452	545346
28-Oct-2020	12435452	564577
30-Oct-2020	53435454	676568
29-Oct-2020	53435454	967567

Now i want to create a summary like the below in power bi.

Outlet	Current Month Sales	Current Month Working Days	Current Month ASPD		Previous Month Sales	Previous Month Working Days	Previous Month ASPD
12435452	676,675	2	338,338		1,642,457	3	547,486
53435454	1,210,820	3	403,607		1,644,135	2	822,068

Current Month is "Nov 2020" & Previous Month is "Oct 2020".

Total Sales = Total Sales column based on Current Month & Previous Month.

Working Days = Distinct Count of Date column based on Current Month & Previous Month.

ASPD (Avg Sale Per Day) = Total Sales /Working Days.

@v-easonf-msft @speedramps

[amitchandak · Accepted solution]

@Jeevan1991 , refer if this file I have used these formula's

[Jeevan1991]

Hi Amit,

This file help me in several other calcualtions, thank you so much.

I will try permotations and combinations.

[amitchandak]

@Jeevan1991 , it should work outlet wise, What is the issue you have facing?

[Jeevan1991]

Hi Amit,

I have used below measure to find Previous Month Total.

Net Sales = SUM(Daily_Outlet_Summary[Saeles])

Previous Month Sales = 

                       PM_SALES = Var CM = MONTH(TODAY())

                            Return

        CALCULATE([Net Sales],FILTER(Daily_Outlet_Summary,MONTH(Daily_Outlet_Summary[DATE])=CM-1))

My worry here is Previous Month total changes dynamically or not using above measures.

[amitchandak]

@Jeevan1991 , With help of time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

You can these columns for work day

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

Working day count

MTD Sales = CALCULATE(SUM('Date'[Work Day]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM('Date'[Work Day]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

till work day this month vs last month

Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))
last Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1  && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))

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.

[Jeevan1991]

Hi Amit,

Thanks for the quick reply.

PREVIOUSMONTH work for dates only i think, but i need outletwise difference. If you could post a screnshot of outletwise table then it will be very useful.

[TomMartens]

Hey @Jeevan1991 ,

This article by @GilbertQ describes how you can create a "period table": Create Dynamic Periods for Fiscal or Calendar Dates in Power BI - Reporting/Analytics Made easy with...

This table allows to have "columns" like "current month" or "previous month" that can be used as slicer content or as column header inside a matrix visual, nested with measures like sales, working days, ...

Hopefully, this provides what you are looking for.

Regards,

Tom