Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I have the following chart and I would like to show the last 2 years with data automatically:
In a post I did some days ago: https://community.powerbi.com/t5/Desktop/Show-last-years-in-a-chart/m-p/1886634#M724593
I had dates until december and I tried to show the last 2 years with an automatically measure.
Thanks to your help I got a way to do it using the following measure:
Measure=
VAR _maxYear =
MAXX ( ALL ( 'Calendar table' ), YEAR ( [Date] ) )
RETURN
IF ( _maxYear - YEAR ( MAX ( 'Sales'[Date] ) ) IN { 0, 1 }, 1, 0 )
The problem I have now is that I have add values until May 2021 and I have a new question.
If I select the month December it just shows the year 2020 (because there is a filter for the last two years and there are no data for December 2021).
Is it possible to filter the last two years with data? So if I select December it shows December 2019 and December 2020 and if I select May it shows May 2020 and May 2021.
Thanks in advance.
Regards.
Solved! Go to Solution.
Hi @PwrBI01 ,
I create some data:
Here are the steps you can follow:
1. Create measure.
Flag =
var _selectedmonth=SELECTEDVALUE('Calendar table'[Month number])
var _maxyear=YEAR(MAXX(ALL(Sales),[Date]))
var _data=SUMX(FILTER(ALL(Sales),YEAR([Date])=_maxyear&&MONTH([Date])=_selectedmonth),[Sales])
return
IF(
_data=BLANK(),
IF(YEAR(MAX('Sales'[Date]))>=_maxyear-2&&YEAR(MAX('Sales'[Date]))<=_maxyear-1&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0),
IF(YEAR(MAX('Sales'[Date]))>=_maxyear-1&&YEAR(MAX('Sales'[Date]))<=_maxyear&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0))
2. Place Measure[Flag] in Filters, set is = 1, and 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 @PwrBI01 ,
I create some data:
Here are the steps you can follow:
1. Create measure.
Flag =
var _selectedmonth=SELECTEDVALUE('Calendar table'[Month number])
var _maxyear=YEAR(MAXX(ALL(Sales),[Date]))
var _data=SUMX(FILTER(ALL(Sales),YEAR([Date])=_maxyear&&MONTH([Date])=_selectedmonth),[Sales])
return
IF(
_data=BLANK(),
IF(YEAR(MAX('Sales'[Date]))>=_maxyear-2&&YEAR(MAX('Sales'[Date]))<=_maxyear-1&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0),
IF(YEAR(MAX('Sales'[Date]))>=_maxyear-1&&YEAR(MAX('Sales'[Date]))<=_maxyear&&MONTH(MAX('Sales'[Date]))=_selectedmonth,1,0))
2. Place Measure[Flag] in Filters, set is = 1, and 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.
Perhaps create a calendar table with only last 2 years and create relationship with your data table
Hi @sanman, thank you for your answer.
My situation is a little bit weird, I want that:
1. If the selected month is included in the last year calendar (for example I have data until May 2021 and I select a month from January to May), the chart should show the Year-To-Date sales from January 2021 to the selected month of 2021 and the Year-To-Date sales of the same period of 2020).
2. If the selected month is not included in the last year calendar (for example if I have data until May 2021 and I select a month from June to December), the chart should show the Year-To-Date Sales from January 2020 to the selected month of 2020 and the same period for 2019.
I think there is not possible to create a calendar table based on a selected value and on an IF function (I tried it and I received an error message)
Regards.
@PwrBI01 , Based on what I got, usually when you select/filter a smaller duration and want to show a larger duration you need to use an independent date table
refer
Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
Hi @amitchandak, thank you for your answer.
My situation is a little bit weird, I want that:
1. If the selected month is included in the last year calendar (for example I have data until May 2021 and I select a month from January to May), the chart should show the Year-To-Date sales from January 2021 to the selected month of 2021 and the Year-To-Date sales of the same period of 2020).
2. If the selected month is not included in the last year calendar (for example if I have data until May 2021 and I select a month from June to December), the chart should show the Year-To-Date Sales from January 2020 to the selected month of 2020 and the same period for 2019.
I have tried to do it with an independent data table but I couldn't do it.
Regards.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |