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
sandeepvig
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
sandeepvig
Frequent Visitor

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

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.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

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

here is the link please find the file attached

 

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

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.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
v-shex-msft
Community Support
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.
sandeepvig
Frequent Visitor

i tried but didn't worked 

@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!

littlemojopuppy
Community Champion
Community Champion

Hi @sandeepvig 

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.

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.