cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronakvi14 Member
Member

Weekday current year vs last year

HI @CheenuSing

 

I have seen lots of posts, but i havent been able to implement this in my case. 

 

basically i want to do this functionality

 

on monday, it should show fridays sales CY and friday sales LY 

on tues, it should show monday sales  CY and Monday sales LY 

 

eg today (monday)  1/21/2019 

DAILY Sales CY  = , it should display last fridays sales i.e  1/18/2019 (3rd week friday )   

DAILY SALES LY =  (1/19/2018) (3rd week friday ) 

because sometimes we have sales on sat/sunday.. 

so i used lastprocessed (date) = returned 1/20/2019 but for sunday of last year ( 1/21/2018) there were no sales ..thereby getting blanks 

 

so i want to create calculated measures/columns 

so that i can create daily sales report for sales, net profit and gross profit 

 

Please help me out. 

 

Sending a screenshot of the report as well as the date 

 

MTD and YTD are calculating fine ( they need to include all sales (including weekends) ) 

 

THANKS A LOT IN ADVANCE 

DATA.jpgREPORT.JPG

6 REPLIES 6
Super User
Super User

Re: Weekday current year vs last year

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

ronakvi14 Member
Member

Re: Weekday current year vs last year

No, 

 

This wont work for me.  MTD and YTD is no problem. What i am struggling with  is 

eg. 

today monday 1/21/2019 

it should fetch friday s total sales for 2019 i.e.  1/18/2019 --> this is working 

for friday of last year i.e. 1/19/2018 --> this is not working 

 

Daily sales report.. Not week. 

 

so how to filter for a particular date ? 

 

CheenuSing Super Contributor
Super Contributor

Re: Weekday current year vs last year

Hi @ronakvi14

 

I am a bit confused about your statement

 

"

because sometimes we have sales on sat/sunday.. 

so i used lastprocessed (date) = returned 1/20/2019 but for sunday of last year ( 1/21/2018) there were no sales ..thereby getting blanks "

 

The blank for previous year is what you expect ? Or you would like to go back further to say 1/20/2018 saturday if it had sales or 1/19/2018 Friday which would have had sales .

 

Kindly calrify.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Community Support Team
Community Support Team

Re: Weekday current year vs last year

Hi @ronakvi14

"

eg today (monday)  1/21/2019 

DAILY Sales CY  = , it should display last fridays sales i.e  1/18/2019 (3rd week friday )   

DAILY SALES LY =  (1/19/2018) (3rd week friday ) 

because sometimes we have sales on sat/sunday.. 

so i used lastprocessed (date) = returned 1/20/2019 but for sunday of last year ( 1/21/2018) there were no sales ..thereby getting blanks 

"

 

Would you like to get MTD or YTD values?

for example ,

eg today (monday)  1/21/2019 

DAILY Sales CY=values from 2019/1/1 to 2019/1/21

DAILY SALES LY =2018/1/1 to 2018/1/21

 

or you just want to get the values on one day?

 

Please clear me.

 

Best Regards

Maggie

ronakvi14 Member
Member

Re: Weekday current year vs last year

Just values of one day. 

"Daily sales "

ronakvi14 Member
Member

Re: Weekday current year vs last year

So @CheenuSing

on monday 1/28/2019 , it should show daily sales of last friday (1/25/2019)  , vs same friday lasy year (1/26/2018)

 

this is resolved 

 

Total Sales CY Yesterday = CALCULATE([Total Sales], LASTDATE('Financial Data'[date_dt]))

Total Sales LY Yesterday = CALCULATE([Total Sales], DATEADD(LASTDATE('Financial Data'[date_dt]),-364,DAY))

 

but i ran into one more issue, if you could help me out 

 

for MTD values 

 

CY : it should calculate till 1/1/2019 to 1/27/2019 -- this is working 

 LY: it should calculate till 1/1/2018 to 1/28/2018 -- per calendar days; but 1/28/2018 is a weekend so the value is coming blank 

 

Total Sales MTD CY = CALCULATE(TOTALMTD([Total Sales],'Financial Data'[date_dt])) -- this is correct 

Total Sales MTD LY = TOTALMTD(SUM('Financial Data'[SaleAmount]), DATEADD(LASTDATE('Financial Data'[date_dt]),-364,DAY))

 

 

 

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 386 members 3,519 guests
Please welcome our newest community members: