SAMEPERIODLASTYEAR is blank

[nhaghani]

Hello,

I am new to power BI

I am trying to get LY YTD for my sales $. I am not getting any error, but just blank data.

I did add below but again it just returns blank with no error. Please help!

ALL(Dates)

[v-yanjiang-msft · Accepted solution]

Hi @nhaghani ,

According to your description, I create a sample.

I think you should put a date column in the visual, the SAMEPERIODLASTYEAR function should be based on an exact date.

LY-YTD Dotcom = CALCULATE(SUM('Ulta Dotcom'[Sales]),SAMEPERIODLASTYEAR('Ulta Dotcom'[Ending-Date]))

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

I have a Target column wherein i have inserted yearly target. in a same column few of my targents are in text and few in % values (Ex- 5 Days, 98%). at the time of transpose data i am getting error. there is a way to work on multiple data type in a same field or column? please help

[Anonymous]

I am getting the Blank values of Total sales of previous year. please help.

 

[nhaghani]

What is your calculation?

you should use sameperiodaslastyear function 

[Anonymous]

Yes, i am already using the same formula....

AP Header Total Sales Previous Year = CALCULATE(SUM('AP Ops Header Level Report- PS'[sum(Amount Invoiced)]),SAMEPERIODLASTYEAR('AP Ops Header Level Report- PS'[[INV-P&I]]Accounting Date (Date)]))

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship from the Accounting Date column of your Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month Name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

Total = SUM('AP Ops Header Level Report- PS'[sum(Amount Invoiced)])

Total in same period last year = calculate([Total],sameperiodlastyear(calendar[date]))

Hope this helps.

[v-yanjiang-msft · Accepted solution]

Hi @nhaghani ,

According to your description, I create a sample.

I think you should put a date column in the visual, the SAMEPERIODLASTYEAR function should be based on an exact date.

LY-YTD Dotcom = CALCULATE(SUM('Ulta Dotcom'[Sales]),SAMEPERIODLASTYEAR('Ulta Dotcom'[Ending-Date]))

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nhaghani]

Hello,

I loaded a calendar for all dates in 2021 and 2022 but I still see full 2021 instead of just up to week 6. 

how do i share my .pbix file here?

please see my sample.. my data should not go past week 6... and it is still doing the same

LY YTD = IF ( MAX ('2022'[ENDING DATE].[Date]) > DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ),CALCULATE(SUM(Sheet1[SALES $]), SAMEPERIODLASTYEAR('2022'[ENDING DATE].[Date])))

[v-yanjiang-msft]

Hi @nhaghani ,

This link talks about how to share a pbix file here.

You should use date in the visual, not week.

Best Regards,
Community Support Team _ kalyj

[nhaghani]

Hello,

Please see this with date below. If this column is YTD, I shouldn’t see 1/23/2021, 1/30/2021, etc…

I would just want it to show 1/2/2021 at this point

[v-yanjiang-msft]

Hi @nhaghani ,

I'm sorry I'm not clear the logic of just show 1/2/2021 at this point.

The function SAMEPERIODLASTYEAR will return a value as long as the same date of last year has a value.

Could you please show more of your sample and expected outcome? This will help resolve your problem faster.

Best Regards,
Community Support Team _ kalyj

[nhaghani]

I expect the data to show last year, Year to Date, meaning I dont want to see anything past today's date for last year. For today i would want to see data up to 1/20/2021 and nothing passed that date. So I dont want to see Feb, March, April, May, June etc for 2021. Just Last Year - Year To Date. 

[v-yanjiang-msft · Accepted solution]

Hi @nhaghani ,

I'm clear, you can modify the formula like this:

LY-YTD Dotcom =
IF (
    MAX ( 'Ulta Dotcom'[Ending-Date] )
        > DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ),
    BLANK (),
    CALCULATE (
        SUM ( 'Ulta Dotcom'[Sales] ),
        SAMEPERIODLASTYEAR ( 'Ulta Dotcom'[Ending-Date] )
    )
)

Best Regards,
Community Support Team _ kalyj

[nhaghani]

I changed the sign and it looks like I am getting there, but it is showing the total for the month of Jan 2021 in stead of just up to today LY... Any suggestions to get it to "Today LY"?

[v-yanjiang-msft]

Hi @nhaghani ,

LY-YTD Dotcom show value for last year of the Date column, you don't want to see anything past today's date for last year, you mean you don't want to see the part framed by the red line, as shown in Case 1 or Case2.

Case1

Case2

My previous formula was for case 2, from your screenshot it looks like this is the case.

If you are now expecting case 1, you should modify the formula like this:

LY-YTD Dotcom =
IF (
    MAX ( 'Ulta Dotcom'[Ending-Date] )
        > TODAY(),
    BLANK (),
    CALCULATE (
        SUM ( 'Ulta Dotcom'[Sales] ),
        SAMEPERIODLASTYEAR ( 'Ulta Dotcom'[Ending-Date] )
    )
)

It should be ">", not "<" in the formula, because you don't want see data past today, and the date column should be a date containing year month day.

Best Regards,
Community Support Team _ kalyj

[nhaghani]

Hello, I tried above and its not returning ANY data...

LY YTD = IF (MAX('2022'[ENDING DATE].[Date])>TODAY(),BLANK(),CALCULATE(SUM(Sheet1[SALES]), SAMEPERIODLASTYEAR('2022'[ENDING DATE].[Date])))

the Ending Date is only on Saturdays, wonderinf if that is causing the issue?

[nhaghani]

hello,

I used the query you sent, its not failing, but its not returning any data... do I need to change anything with the date part? 

[nhaghani]

Also I wanted to let you know, my "Ending Date".Date is only showing Saturday's date for Last year and today...  Could this be causing the issue for data not to show up?

[nhaghani]

this is my calendar:

[nhaghani]

I created a calendar with all dates for 2021 and 2022.

Below on the left is what I would like to see, but I am currently getting what is on the right. Now my YTD is repeating for future weeks...

Here are my calculations:

YTD =TOTALYTD(SUM('Sheet1'[SALES $]), '2022'[ENDING DATE])

LY YTD = IF ( MAX ('2022'[ENDING DATE]) > TODAY(), BLANK (),CALCULATE(SUM(Sheet1[SALES $]), SAMEPERIODLASTYEAR('2022'[ENDING DATE])))

[v-yanjiang-msft]

Hi @nhaghani ,

In the visual, you should put the date column, not the WK, also, if you only has date in saturday, the SAMEPERIODLASTYEAR will return value only when it has a value in the date column in the same date last year.

For example, for date 2022/2/8, if 2021/2/8 isn't in your date column, the LY-YTD will not return value.

Best Regards,
Community Support Team _ kalyj