Show Current Month, Previous Month, 6 Month Average in table

[Anonymous]

Hi all,

I would like some ideas on how to set up the following report in Power BI:

I want KPI names on rows (ex. Sales, Costs, etc.), and the period on the columns, as the following image:

Can anyone suggest ways on how best to achieve this please? I have a star schema model behind the scenes with the fact table for transactions as a source for this table.

All the help is appreciated!

Thanks

[v-xicai]

Hi @Anonymous ,

You may create calendar table first of all, create relationship with your fact table, then create measures in your fact table like DAX below.

Calendar=CALENDARAUTO()
 
Current Month= CALCULATE(SUM(Table1[Sale]),FILTER(ALLSELECTED(Table1), YEAR(Table1[Date]) =YEAR(MAX(Table1[Date]))&&MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))))
 
Previous Month= = CALCULATE(SUM(Table1[Sale]),FILTER(ALLSELECTED(Table1), YEAR(Table1[Date]) =YEAR(MAX(Table1[Date]))&&MONTH(Table1[Date]) =MONTH(MAX(Table1[Date]))-1))
 
Prev 6 Months average = CALCULATE ( SUM ( Table1[Sale] ), DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -6, MONTH ) )

Best Regards,

Amy

Community Support Team _ Amy

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

[amitchandak]

Create a date table after that you can use formulas like

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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))


Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))  
Rolling last 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)),-3,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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601