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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JGSingson
Regular Visitor

How do you align two dates in one calendar?

Hi, I have a problem with where I need to get the value from its Same Day Last Year we have a calendar table in which we have DATE as our primary date and have a column called SAME DAY LY, the issue here is how I can I use our transactions to read SAME DAY LY all the while still pointing at the dates filtered (Jan 01, 2022, & Jan 02, 2022)?

JGSingson_3-1674007553330.png


in this image, what I want to achieve is similar to using the SAMEPERIODLASTYEAR function the only issue is that I do not know an alternative, I used DATEADD but what happened is it pivoted the Date instead and added Jan 03, 2020, and Jan 04, 2020, to the dates instead of it posting in the row of Jan 01, 2021, and Jan 02, 2021.

 

2 REPLIES 2
SamInogic
Super User
Super User

Hi @JGSingson,

As per our understanding, you want to get a Last Year date with same day and month so can try below DAX expression to create new column with your table.


Previous Year Date = DATE(YEAR([Date]) -1 , MONTH([Date]), DAY([Date]))

 

Please refer to the below screenshot for the same.

SamInogic_0-1674018252437.png


Let us know if this works for your requirement.


If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Hi, @SamInogic sorry I might have worded it wrongly, but what I am trying to do is quite literally the same day last year, e.g December Wk1 Sunday 2021 = December Wk1 Sunday 2022 something like this.

But on the question above I already have as a given the [SAME DAY PY] what I want to know is if it is possible to use [SAME DAY PY] on a calculation e.g Sales, and given that the table has [DATE] as part of the row, show both calculations of Sales on the Selected[DATE] e.g Jan 1 & 2 2022. 

Highlighted Red to Transfer at Highlighted Yellow.

JGSingson_1-1674020281815.png

 


1ST COLUMN FORMULA = SUM ( Sales )

2ND COLUMN FORMULA = Sales Same Day PY= CALCULATE([Sales],DATEADD(vw_d_calendar[Date],MAX(vw_d_calendar[Date Difference]),DAY))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors