Same period last year column

[Akhil_1411]

Hi Everyone,

I am trying to create same period last year on a column(need to use this value as a slicer) but it is not giving the right result. Can anyone help me with this?

Formula:- 

Same Period Last Quarter col =

Var A= CALCULATE(SUM(Merge1[Unearned Disc]),ALLEXCEPT(Merge1,Merge1[Billing_Doc_Key]))

Var B = CALCULATE(A,SAMEPERIODLASTYEAR('Date new'[Date]))

return B

In the above calculated column i wanted the unearned disc value for same period last year.

Thanks,

Akhil.

[v-shex-msft]

HI @Akhil_1411,

I'd like to suggest you use date function to manually calculate the date range and use it in your formula:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[amitchandak]

@Akhil_1411 , Try with date date

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

SAMEPERIODLASTYEAR will also work with date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Akhil_1411]

@amitchandak ,

The logics which u have provided is for measures but here i am trying to create a column. Can you please look into it.

I want to create a column not a measure😐

Thanks,

Akhil.

[sturlaws]

Hi @Akhil_1411 

Do you still have issues with this?

If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!

Cheers,
Sturla

[DataZoe]

Hi @Akhil_1411,

Is the 'Date new' marked as a date table in the ribbon?

[Akhil_1411]

@DataZoe Yes. it is marked as a datetable.

[DataZoe]

@Akhil_1411 ,

I see you said you were creating a calculated column, have you tried this as a measure instead?