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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DarylM
Helper II
Helper II

Sales from Start Date to Today's Date Prior Year

Hi,

I am trying to calculate the total sales from 8/1/2016 to Today's date last year but dynamically so it would work with a date slicer 'before" date.  The formula works, but the total does not change when I change the end date on the slicer. It seems to be stuck on TODAY no matter the date range. I don;t want to specify a end date as I want it to update autmatically on refresh daily. 

Any help would be appreciated. 

 

Total PY Sales = CALCULATE(SUM('PRODDTA F42119'[2016 SCH Sales]),SAMEPERIODLASTYEAR(DATESBETWEEN('PRODDTA F42119'[G/L Date].[Date],"8/1/2017",TODAY())))

2017-08-17 09_25_57-MC2 Tracker v4.0 - Power BI Desktop.jpg

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

 

HI @DarylM,


Based on test, today function not works in datebetween function, I'd like to suggest you to manual calculate date range to instead the 'datebetween' function.

Dynamic = CALCULATE(SUM('Sample'[Sales]),FILTER(ALL('CALENDAR'),[Date]>=DATE(2016,8,1)&&[Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

 

HI @DarylM,


Based on test, today function not works in datebetween function, I'd like to suggest you to manual calculate date range to instead the 'datebetween' function.

Dynamic = CALCULATE(SUM('Sample'[Sales]),FILTER(ALL('CALENDAR'),[Date]>=DATE(2016,8,1)&&[Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
DarylM
Helper II
Helper II

I would like to be able to slide the slicer back to view previous data which is why I am trying to find a dynamic way to calculate the data. If I use the MAX(End of Month) Same Time last Year, I get the whole total from 8/1 to 12/31 instead of just the last GL date as of the current year for last year. Hope this makes sense. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.