Reply
Regular Visitor
Posts: 27
Registered: ‎08-13-2018

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

Super User
Posts: 10,476
Registered: ‎07-11-2015

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


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

Proud to be a Datanaut!


Highlighted
Regular Visitor
Posts: 27
Registered: ‎08-13-2018

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 ? 

 

Super User
Posts: 596
Registered: ‎02-17-2016

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

Community Support Team
Posts: 2,482
Registered: ‎03-15-2018

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

Regular Visitor
Posts: 27
Registered: ‎08-13-2018

Re: Weekday current year vs last year

Just values of one day. 

"Daily sales "

Regular Visitor
Posts: 27
Registered: ‎08-13-2018

Re: Weekday current year vs last year

[ Edited ]

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))