Sales Period To Date

[thorpyuk]

Hi all, i'm new to Power BI, and although i've had some training, will need some time to get used to DAX & working with PBI.

I've started to replicate a sales report, and i'm struggling with 'sales period to date'.

I have created a measure which does work for this, but my problem is getting it to work with a week slicer i have on my page. My current formula is 

NetSalesIncPTD = SUMX ( VALUES ( 'db_v_BaseCalendar_TYLY'[Period] ), [NetSalesIncM] )

But this only works if there's no week selected on my week slicer. I did think about editing the interactions, but i do need a link there because i want it to show 'period to date' based on the week that i have selected. EG, if i have week 2 of period 6 selected, i want to sum weeks 1 and 2. If i have week 4 of the period selected, i want to sum weeks 1 through 4 of period 5 etc.

Thanks in advance for your help!

[amitchandak]

I am not sure what you want. can you explain with example. In case you need to deal with weeks refer to this file.

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

For other time and even for a week make sure you have a date calendar in place.

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))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(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 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)))


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/

Appreciate your Kudos.

[thorpyuk]

Hi Amitchandack, i do have a calendar already - snapshot of data format below (I believe the actual 'date' field is called 'TransDate', it's formatted as date/time and setup as a date hierarchy). My slicer is based on 'WeekFullDesc' - i want to sum all sales in the period to date that relates to the selected week. EG, the first date is in week 5, which happens to be the first week of period 2, so would sum just that 1 week once selected. If i picked week 6 however, i want it to sum weeks 5 and 6..

[amitchandak · Accepted solution]

Create a rank based on week start date in your date table and try

Measure =
var _week = maxx(Date,Date[Week full Desc])
var _rank = maxx(filter(Date,Date[Week full Desc]=_week),Date[Rank])
var _prd = maxx(filter(Date,Date[Week full Desc]=_week),Date[Period])
return
calculate(sum(Sales[Sales]), filter(Date, Date[Period] =_prd && Date[Rank] <=_rank))

Attaching the file to refer week rank

Appreciate your Kudos.

[amitchandak]

@thorpyuk 

Did the last solution help?

Appreciate your Kudos.

[thorpyuk]

Thanks amitchandak, thats fantastic! Appreciate all your help