cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GustavO65
Helper II
Helper II

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

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

BranchesUnitsMatterPrevious UnitsPrevious Amount
Local A12001250001000130000
Local B8503560001200650312

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

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

View solution in original post

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. 

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

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

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!

 

@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 TableFact Table

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

Calendario TableCalendario Table

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

RelationshipRelationship

 

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

 

FormulaFormula

 

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)

 

ConsultaConsulta

 

 

 

 

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

 

variables.PNG

 

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"...

 

nosplit.PNG

 

 

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

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

Hi @yingyinr 

"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..

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. 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.