cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lord_Zeus Frequent Visitor
Frequent Visitor

Calculate same perioad last year ( with calendarauto)

Hi all! This problem takes 2,5 days my life, and i'm can't pay more... =))

Many posts abw it problem and i'm dont find solution for my case. May be i'm dont understand something, i'm need help.

 

Im have model and i'm using calendarauto. He makes a full date calendar with end of year. For example i'm have model, and orders ends 07.2007. Calendar have dates ends in 12.2007.

 

So, when i'm try to calculate LYTD sum i'm get the kpi sum not from 01.2006 - 07.2006 but 01.2006 - 12.2006, and my current period is 01.2007 - 07.2007. Model ends in 07.2007.

 

So, how can i'm calculate right period. 2 ways i'm think: cut in calendar not used dates ( where no sales) and in mesure use filter to filtered them max month in current year or somthing like that. But im faild in try solve it.

 

2019-02-17 20_23_22-Window.png

Продажи = Sales

"Продажи LYTD 2" - it is a measure im try to write.
3 sheet is a temp for demo this problem.
Link of Power Bi file here.
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate same perioad last year ( with calendarauto)

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Calculate same perioad last year ( with calendarauto)

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Lord_Zeus Frequent Visitor
Frequent Visitor

Re: Calculate same perioad last year ( with calendarauto)

Thanks! But i'm a little change code, in my model i'm dont need the tables only calendar. I'm use filter like you and make calculation on 2 vars results. All work, and its grate.

 

Продажи LYTD = 
VAR __MaxYear = MAX('Календарь'[год])
VAR __CurrentMonth = MONTH('Заказы'[MaxOrderDate])
RETURN SUMX(FILTER('Календарь';
'Календарь'[год]=__MaxYear-1 && 'Календарь'[месяц] <= __CurrentMonth);
'Детали заказов'[Сумма продажи])

1. I'm take current year

2. Take a month with real orders ( table "Заказы")

3. use filter to colculate Sum of sales last year to date in corrent year