Measure for calculating the same week and same month last year

[ckleppe]

Hi,

I'm building a report for the weekly sales for my company. 

In the report I calculated the values: Actual, budget and last year (and also the YTD versions).

The idea for the report is a weekly and monthly update. At first I had a problem with the week figures, because I used SAMEPERIODLASTYEAR, but the dates of the weeks are different between the years.

So, after checking the Forums, the week update works with the measure: 

Gewicht VJ = CALCULATE(SUMX(Statistiek_ALL;Statistiek_ALL[Gewicht]);DATEADD(DATEADD(Periode[Datum];-1;YEAR);1;DAY))

But when I'm selecting the month, this measure doesn't give the result I need.

Can someone help with with calculating a measure that covers both options?

Thanks!

[amitchandak · Accepted solution]

@ckleppe 

Same week date is 364 days behind

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

this can be use in week wise display.

You can also Week Vs 52 week behind using rank refer :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

For the month you can have Year behind measure

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

Or MTD formula

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))))

 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/

[ckleppe]

@amitchandak thanks for the reply, however I'm looking for 1 measure for both options. I already have the measures for week and month seperate. Is this also possible? 

For example I have these filters:

(Year, Week, Month)

I would like to use the both filters in one report (the week filter every week, the month filter once a month)

Is this possible in one report, or do I need to make a Week report and a seperate Month report?