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
Paulyeo11
Impactful Individual
Impactful Individual

when when i set Master calendar end date to 2021 , i cannot view the sales LYTD ?

Hi All

 

All the while master cal end date i set to 2020 , But i need to add sales target amount 2021 , in order to make sales target for 2021 appear , i need to set master cal end date to 2021 , but when sales LYTD amount display null. see below image :- 

Paulyeo11_1-1608674251122.png

 

When i Set the master Cal end date to 2020 , I cannot view the sales Target 2021 amount But Sales LYTD is okay :-

 

Paulyeo11_0-1608673941086.png

Hope some one can share with me where go wrong ?

 

Paul

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Paulyeo11  What is the measure for your Sales LYTD please? 

 

You should add a date context to the visual or report, so that the table knows what year you want to display sales for, otherwise time intelligence functions don't behave as we expect.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

9 REPLIES 9
aj1973
Community Champion
Community Champion

Paul,

 

It's not clear what you want to achieve. You want to calculate 2 measures one in the future and the other a running total in the past! whats the relationship between the 2 measures!

Attach a PIBX please

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

Thank you for sharing.  I think Allison have pointed out the issue , i should not change the Master Cal end date , instead i should change the 2021 sales target expression :-

From :- 
Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))
To :-
Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-0,YEAR)))
 
I just tested it work fine now.
 
Paul Yeo

 

aj1973
Community Champion
Community Champion

Glad to hear it even though i am skeptical in regards of the use of your DAX formulas.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Paulyeo11
Impactful Individual
Impactful Individual

Hi AJ

Any way i am very appreciated very much you have help me solve few of the major issue.

Paul

AllisonKennedy
Super User
Super User

@Paulyeo11  What is the measure for your Sales LYTD please? 

 

You should add a date context to the visual or report, so that the table knows what year you want to display sales for, otherwise time intelligence functions don't behave as we expect.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison

You are good , you spot my mistake , now working fine.

 
So the below expression on 2020 , this year number i should not change right ? what about during 2021 Jan , if still at 2020 , will the YTD sales and LYTD sales amount still able to get 2021 Sales amount ? 
 
Any link to share with me what is this date 2020 all about ? so i can understand.
Date = CALENDAR(Date(2000,01,01),date(2020,12,31))
 
Paul Yeo
 

Hi Allison

Thank you for your sharing.

 

I am lhearing to you said that i should not change the Master Cal date from 2020 to 2021 right ? instead i should focus on the 2021 sales target expression below to get the 2021 sales target right ? :- 

 

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))
 
it is correct ?
Paul Yeo

@Paulyeo11  It is okay to change the calendar to include future dates (it often should) however, the Sales LYTD measure is the broken one in that case, so what is that formula please?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison

 
Below is my expression for Sales LYTD :-
 
Sales LYTD =
var _max = date(year(today())-1,month(today()),day(today()))
return
TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
 

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.