Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Gingerjeans88
Helper IV
Helper IV

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

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

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.BI Screenshot.png

 

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
)

 

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
Super User
Super User

@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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.