Forum Discussion
SSAS tabular cube previous month data
Hi team,
below is my data and yellow color is my expected output. i want to get previous month data.
in below "Total" column is my measure and i want to get lag/previous month measure "Previous Month total" data, and in my table we have duplicate date column. when i extract distinct date column into new table "Dim Date" and mapped on date column with actual table and write measure i am not getting exact results.
my DAX query:
previous Year:= YEAR(TODAY())-1 PreviousMonth:=
total := CALCULATE(SUM(DIM_FRM_REVENUE[Revenue]),FILTER(DIM_REVENUE,MONTH(TODAY())=MONTH(DIM_REVENUE[Value_Date])))
Total Previous Month:= CALCULATE(SUM(DIM_REVENUE[Revenue]),FILTER(DIM_REVENUE,(MONTH(DIM_REVENUE[Value_Date]) = [PreviousMnth]) && (YEAR(DIM_REVENUE[Value_Date] = [PreviousYear]))))
would you please help me correcting my output.
Regards,
snkm
Hi Anonymous ,
Sorry, please update the measure as below.
Total Previous Month = VAR pre = EDATE ( MAX('DIM_REVENUE'[date]), -1 ) VAR preym = FORMAT ( pre, "mmm yyyy" ) RETURN CALCULATE ( SUM ( DIM_REVENUE[Revenue] ), FILTER ( ALLSELECTED(DIM_REVENUE), DIM_REVENUE[Month Name] = preym ) )Pbix as attached.
8 Replies
Hi Anonymous
Try PARALLELPERIOD DAX function
https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInAnonymous
If you have date, you can try Time intelligence With date calendar
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 Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr)))) 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))) Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER)))) Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,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))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year)))) trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER)) trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER)) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/You should create date table and mark it as date table. This table should contain a continious set of dates (no gaps). See: https://docs.microsoft.com/en-us/power-bi/desktop-date-tables. You could also create the date table using DAX, for example with: https://docs.microsoft.com/en-us/dax/calendarauto-function-dax.
This is an example you can use if you create a new table:
DateTable = CALENDARAUTO()
If you've created this date table (and linked it to your table with totals) you can use time intelligence functions such as YTD or PREVIOUSMONTH. In your case you could solve your question by using PREVIOUSMONTH. See the example below:
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTable'[Date]))Hi Larssb,
Thanks a lot for you resonding and upates. below are my findings
i have tried with suggestion provide using DIMTable= Calenderauto(12) and mark table as Date.
used most of the time intelligence function and all measure are showing blank. would you please help in suggesting other ways.
Regards,
Snkm
Hi Anonymous ,
Sorry, please update the measure as below.
Total Previous Month = VAR pre = EDATE ( MAX('DIM_REVENUE'[date]), -1 ) VAR preym = FORMAT ( pre, "mmm yyyy" ) RETURN CALCULATE ( SUM ( DIM_REVENUE[Revenue] ), FILTER ( ALLSELECTED(DIM_REVENUE), DIM_REVENUE[Month Name] = preym ) )Pbix as attached.
Hi Anonymous ,
Please try this one please.
Total Previous Month = VAR pre = EDATE ( TODAY (), -1 ) VAR preym = FORMAT ( pre, "mmm yyyy" ) RETURN CALCULATE ( SUM ( DIM_REVENUE[Revenue] ), FILTER ( DIM_REVENUE, DIM_REVENUE[Month Name] = preym ) )Hi v-frfei-msft ,
i have implemented same query given by you and below is the output. i am getting only one month data. would you please suggest any more methods to get all month data.
Regards,
snkm
