Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning!
We are dealing with a problem when we try to add the preivous period sales (year) when we select a date from a date range.
So, the first step was add a table to identify de date ranges and make the relation with our calendar table.
Period =
VAR _DateTable = CALENDAR(DATE(2020,1,1),TODAY())
VAR _Today = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = 0),"RelativeDate","Today","Sort",1)
VAR Yesterday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = -1),"RelativeDate","Yesterday","Sort",2)
VAR LastWeekday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1),[Date],DAY) = 0),"RelativeDate","LastWeekday","Sort",3)
VAR ThisWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = 0),"RelativeDate","ThisWeek","Sort",4)
VAR LastWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = -1),"RelativeDate","LastWeek","Sort",5)
VAR ThisMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = 0),"RelativeDate","ThisMonth","Sort",6)
VAR LastMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = -1),"RelativeDate","LastMonth","Sort",7)
VAR ThisYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = 0),"RelativeDate","ThisYear","Sort",8)
VAR LastYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = -1),"RelativeDate","LastYear","Sort",9)
Return
UNION(_Today,Yesterday,LastWeekday,ThisWeek,LastWeek,ThisMonth,LastMonth,ThisYear,LastYear)
When we create a metric for sales, everything is working fine. However when we create a previous period metric and add to visual, it goes blank when you select a period.
Any help on this?
Kind regards
ATO
Solved! Go to Solution.
Hi @ATO23 ,
I created some data:
Here are the steps you can follow:
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Period'[RelativeDate])
var _date=CALCULATE(MAX('Period'[Date]),FILTER(ALL(Period),'Period'[RelativeDate]=_select))
return
IF(
AND( MAX('Table'[Year])=YEAR(_date),MAX('Table'[Month])=MONTH(_date))||AND(MAX('Table'[Year])=YEAR(_date)-1,MAX('Table'[Month])=MONTH(_date))
,1,0)
Measure =
SUMX(
FILTER( ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])),[amount])
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @ATO23 ,
I created some data:
Here are the steps you can follow:
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Period'[RelativeDate])
var _date=CALCULATE(MAX('Period'[Date]),FILTER(ALL(Period),'Period'[RelativeDate]=_select))
return
IF(
AND( MAX('Table'[Year])=YEAR(_date),MAX('Table'[Month])=MONTH(_date))||AND(MAX('Table'[Year])=YEAR(_date)-1,MAX('Table'[Month])=MONTH(_date))
,1,0)
Measure =
SUMX(
FILTER( ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])),[amount])
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello, thanks for your answer. We have already the metric create for the previous period, the same way you wrote down, the problem is that metric doesn't work with a date range selector. See example:
I have selected i want to see the sales for this month (may 2022) and i want to compare it with the sales of May 2021. and the metric is not working in with the selection date range
@ATO23 , Not very clear. But you can get previous year using Date table like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |