Show previous year's sales, but only within the filter date range

[GustavO65]

Dear forum..

I am new with the use of PowerBI and am testing it with the aim of using it in my professional work.

So far watching videos and reading different posts or tutorials I was able to advance the knowledge of the tool.

But I'm stuck in something that I've been spinning for several days and I can't figure it out.

For most of the customers I have it is very important (vital) to always compare the sales metrics (units and amount) with the same period but the previous year.

When I refer to "period" I am indicating a date range "From"/"To" applied as a filter by whoever uses the report.

For example, if you want to see sales by branch (units and amounts) from 15/02/2020 to 31/03/2020, I must somehow get sales from 15/02/2019 to 31/03/2019.

The goal is to get such a report. (e.g.)

Sales from 15/02/2020 to 31/03/2020

Branches	Units	Matter	Previous Units	Previous Amount
Local A	1200	125000	1000	130000
Local B	850	356000	1200	650312

All sales data is in a single SQL Server table that each record is the detail of all billing receipts.

Date, Customer, No Proof, Item, Unit Price, Units, Amount, etc...

I have tried with dateadd, sameperiodolastyear, etc. etc. etc... But with none of them I managed to get the previous year's sales correctly

I hope someone can help me with this.

Thank you very much

[amitchandak · Accepted solution]

@GustavO65 , Make sure you do it with date calendar

LYTD  forced= 
var _max1 =maxx(allselected('Date'),[date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
var _min1 =minx(allselected('Date'),[date])
var _min = date(year(_min1)-1,month(_min1),day(_min1))
return
CALCULATE(Sum('Table'[Units]),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

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/

[amitchandak · Accepted solution]

@GustavO65 , Make sure you do it with date calendar

LYTD  forced= 
var _max1 =maxx(allselected('Date'),[date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
var _min1 =minx(allselected('Date'),[date])
var _min = date(year(_min1)-1,month(_min1),day(_min1))
return
CALCULATE(Sum('Table'[Units]),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

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/

[GustavO65]

Hi @amitchandak 

Tnks for your answer..

I tried with your solution but doesn't work.. Just to be clear about your formula...

maxx(allselected('Date'),[date])

When you refers to allselected('Date'),[date].. 'Date' is the calendar table, Isn´t?

Also in sum('Table',[units]..... 'Table' is the fact table, Isn´t?

I'll continue working around your formula....

Regards!

[GustavO65]

@amitchandak 

Hi.. I give you more information...

In my fact table date field (called Fecha) looks like this... (is a DateTime).. The Fact table is called Ventas_Detalle_Para_Informes

Fact Table

This is my 'Date' (Calendar) table.. The name for this table is "Calendario"..

Calendario Table

This is the relationship between Fact table and Calendar table...

Relationship

This is the calculate field I made using your formula...

Formula

But when I tried to use Vta_Iva_LY there are not any result...

I´m selected sales between 04/01/2020 and 04/29/2020 (In fact table there are sales information from 04/01/2019 and 04/29/2019)

Consulta

[GustavO65]

Hi.. @amitchandak 

Working with the formula I discover that the variable values are not taking the value of the filter applied..

As you can see the filter applied is from 04/01/2020 to 04/29/2020.
But the variables values in the formula is taking the first and last date of my fact table..
I don´t know how to get values from filters applied

[GustavO65]

Hi.. 

Hi I get the correct value referenced the formula only with the fact table...

So I use this formula to get the correctly same period (like as filtered) of last year...

Vta_Iva_LY = var _max1 =maxx(allselected('VENTAS_DETALLE_PARA_INFORMES'),[fecha])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
var _min1 =minx(allselected('VENTAS_DETALLE_PARA_INFORMES'),[fecha])
var _min = date(year(_min1)-1,month(_min1),day(_min1))
return
CALCULATE(Sum('VENTAS_DETALLE_PARA_INFORMES'[venta_con_iva]),filter(all('VENTAS_DETALLE_PARA_INFORMES'),'VENTAS_DETALLE_PARA_INFORMES'[fecha]<=_max && 'VENTAS_DETALLE_PARA_INFORMES'[fecha]>=_min))

Now I the problem was that do not split sales value between our differents "Canal"...

[v-yiruan-msft]

Hi @GustavO65 ,

Could you please provide more deatils on your current problem? What's the meaning of "not split sales value"?

Now I the problem was that do not split sales value between our differents "Canal"...

---

Best Regards

Rena

[GustavO65]

Hi @v-yiruan-msft 

"LOCALES PROPIOS" AND "OTROS CANALES" are referred to our stores agrupation.
I´m applying date filter between 04/01/2020 and 04/29/2020 so in the matrix you can see 'venta_con_iva' = 10.447.041 is the total sale for our stores gruped as "LOCALES PROPIOS", also 'venta_con_iva' = (408.288) is the total sale for our stores gruped as "OTROS CANALES"..
But 'Vta Iva LY' is displaying "TOTAL" sales between 04/01/2019 and 04/29/2019, but I must display 'Vta Iva LY' value for "LOCALES PROPIOS" and the value for "OTROS CANALES".
I hope to be clear..

[jstorm · Accepted solution]

You should be able to use DATEADD or SAMEPERIODLASTYEAR with your measure 'vita con iva' if the dates and relationships are correct. 

You can try using this code to generate a different dates table (if there is a problem with your current dates table):

= (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY

Then create a relationship with your sales table and try using the measures @Ashish_Mathur recommended. 

Check to make sure your data types are correct in both tables for the dates column.  Make sure you are using your dates table for the slicer. 

[GustavO65]

TNKS A LOT @jstorm ...

You gave me the key with this comment!.. Make sure you are using your dates table for the slicer

Because I was using the fact table...

So I made changes in the @amitchandak formula.. Now this is the formula which is working properly...!

Vta_Iva_LY = var _max1 =maxx(allselected('Fact Table'),[fecha])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
var _min1 =minx(allselected('Fact Table'),[fecha])
var _min = date(year(_min1)-1,month(_min1),day(_min1))
return
CALCULATE([venta_con_iva_suma],filter(all('Calendario'),'Calendario'[fecha]<=_max && 'Calendario'[fecha]>=_min), sameperiodlastyear(Calendario[fecha]))

Tnks to all for your help!! I hope in the future I can help you...

Best regards

[Ashish_Mathur]

Hi,

Try this

1. Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table
2. Build your slicer from the Calendar Table
3. Write these measures: Total amount = SUM(Data[Amount]) and Total amount in same period last year = CALCULATE([Total amount],SAMEPERIODLASTYEAR(Calendar[Date]))

Hope this helps.