Number of Sales by month from previous year, only up to same day last year

[Gingerjeans88]

Hi there,

After a little guidance, please folks! 

I have a simple clustered column chart - first set of values is easy and done. Count of Sales in this Financial Year. 

The second, however, I THINK is simple but I am overthinking it. 

I want to count number of sales by month again, but for last financial year (I have a Date table with a custom Financial Year column, the FY starts in September) and up until the same date last year. Ie. if we are on 12th October this year I want to see all Sales created from 1st to 12th October, PLUS all the sales created last year from 1st to 12th October. 

Help please!

THanks,

Lynn

[Ashish_Mathur]

Hi,

In the date slicer (which you have dragged from the Calendar Table), you select 12 October 2020.  To get sales from Oct 1, 2019 to Oct 12, 2019, write this measure

=calculate([total sales],datesbetween(calendar[date],EOMONTH(EDATE(MIN(Calendar[date]),-12),-1)+1,EDATE(MIN(Calendar[date]),-12)))

Hope this helps.

[Gingerjeans88]

Thank you both so much. However, I have since had the requirement changed to weekly. Argh.

I have a date table with a custom fiscal week column, but I also have the custom fiscal week column in my fact table. 

I want to show Current Fiscal Year sales by Fiscal Week (easy) AND Previous Fiscal Year sales by Fiscal Week....but only up to the same point in time. Ie. If we are only halfway through September, I only want to see last year's sales for half of September. 

HELP! I have got the sales by week sort of working (see attached picture), but I don't want all of the rest of Week 5's data until week 5 this year is complete (if that makes sense)....nor do I want to see any of the subsequent weeks from last year because we have not reached that point in time this year. 

FYI my fiscal year starts on 1st September.

This is currently built on a custom column in my date table that determines if the date is past ('Is Past') but it doesn't appear to have worked (DAX below). Then the measure to sum up the sales is also below:

IsPast =

VAR LastAppDate = MAX ( Applications[Date Submitted].[Date] )

VAR LastAppDatePY = EDATE ( LastAppDate, - 12 )

RETURN

(Applications[Date Submitted].[Date] <= LastAppDatePY)

Adjusted Previous Year Sales =

CALCULATE(

[No of Applications],

SAMEPERIODLASTYEAR ( 'Dates'[Date] ),

'Applications'[IsPast]= TRUE

)

[v-kelly-msft]

Hi  @Gingerjeans88 ,

Could you pls upload your .pbix file to onedrive business and share the link with us?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Gingerjeans88 , Ideally you should get it with datesmtd with date table. But this will create issue when you view data by month

example

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

LYMTD QTY forced=
var _dt = today() // Or // maxx(allselected(Date),Date[Date])
var _max = date(_dt)-1,month(_dt),day(_dt)) //
return

if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())

LMTD QTY forced= //Last month
var _dt = today() // Or // maxx(allselected(Date),Date[Date])
var _max = date(_dt),month(_dt)-1,day(_dt)) //
return

if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())

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


Appreciate your Kudos.