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

[sandeepvig]

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.

[sandeepvig]

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

[v-shex-msft]

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

[sandeepvig]

here is the link please find the file attached

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

[v-shex-msft]

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

[Greg_Deckler]

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

[v-shex-msft]

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

[sandeepvig]

i tried but didn't worked 

[littlemojopuppy]

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

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.