cancel
Showing results 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.

Frequent Visitor

how do i get last week sale for same period when I don't have the dates column

https://techcommunity.microsoft.com/t5/sharepoint-bi/how-do-i-get-last-week-sale-for-same-period-whe...

Hi there,

I have data in this format:

• One column has the year.
• The second column has the period (12 periods in a year, but they are not the same as months, as they start on different dates).
• The third column has the week number (every period has at least 4 weeks; some of them have 5 weeks).

I'm trying to create filters for the data:

The first filter is the year, followed by the period and then the week. The data I get after applying these filters is correct.

Now, I've tried using the 'sameperiodlastyear' function to retrieve data from the previous year with the same period and week, but it's not working. Can you please help me?

Thanks.

9 REPLIES 9
Frequent Visitor

sorry i am beginer in powerbi adn the video is way advanced.

Community Support

Hi @sandeepvig ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

Regards,

Xiaoxin Sheng

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

https://ucarecdn.com/a83b3955-77a9-4be2-bf53-88f988869866/

Community Support

HI @sandeepvig,

You can try to use the following measure formula if they suitable for your requirement:

``````CY Sales UM =
CALCULATE (
SUM ( 'UM WEEKLY'[Sales] ),
ALLSELECTED ( 'UM WEEKLY' ),
VALUES ( 'UM WEEKLY'[Year] ),
VALUES ( 'UM WEEKLY'[period] ),
VALUES ( 'UM WEEKLY'[week] )
)

PY Sales UM =
VAR currYear =
MAX ( 'UM WEEKLY'[Year] )
RETURN
CALCULATE (
SUM ( 'UM WEEKLY'[Sales] ),
FILTER ( ALLSELECTED ( 'UM WEEKLY' ), 'UM WEEKLY'[Year] = currYear - 1 ),
VALUES ( 'UM WEEKLY'[period] ),
VALUES ( 'UM WEEKLY'[week] )
)``````

Regards,

Xiaoxin Sheng

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

@sandeepvig See if this video helps, it has week examples like what you want.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Community Support

HI @sandeepvig,

Time intelligence functions should base on the hidden calendar table so I think you can't directly use it to work with a fiscal date field value.

For this scenario, you can try to extract the current date value and use date function to manually define the filter ranges based on that variable.

Time Intelligence "The Hard Way" (TITHW)

Regards,
Xiaoxin Sheng

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

i tried but didn't worked

Community Champion

@sandeepvig that's unfortunate because you must have a use case not anticipated by everyone who has ever used Power BI.  I would have thought that a date table that included fields such as Calendar Year/Month/Week and Fiscal Year/Month/Week (because you appear to have a 4-4-5 fiscal calendar) and it was marked as a date table it would be very easy to solve this with some time intelligence functions.  But apparently your use case is something brand new.  Since this is a completely new, unanticipated use case I would suggest raising an idea at https://ideas.powerbi.com.  Maybe a future solution can be developed that remedies your needs.  Because, as you said, "i (sic) tried but didn't worked (sic)".

@Ashish_Mathur is excellent at this kind of thing.  I'm certain he'd love to help you!

Community Champion

It's almost a requirement that every data model has a date table.  If you had one and it were marked appropriately, this would be very easy to solve.  Highly recommend incorporating one into your model.

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors