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.
Hi Team,
Wanted to Show last 12 months from previous month based on slicer selection.
(For ex: if i select 2020 year in slicer then will show last 12 months (2010 Dec,2019 Nov,2020 Jan,2020 Feb,....2010 Oct) from previous month of 2020 )
Year Slicer
Expected Output
Thanks In Advance
Siddanth.
Solved! Go to Solution.
Hi, @Anonymous
You can try this:
Measure3 =
IF (
SELECTEDVALUE ( 'Table'[year] ) <> YEAR ( TODAY () ),
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) )
),
1,
0
),
IF (
SELECTEDVALUE ( Sheet1[monthnumberofyear] )
<= MONTH ( TODAY () ) - 1,
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) )
),
1,
0
),
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) - 1 )
),
1,
0
)
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can try this:
Measure3 =
IF (
SELECTEDVALUE ( 'Table'[year] ) <> YEAR ( TODAY () ),
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) )
),
1,
0
),
IF (
SELECTEDVALUE ( Sheet1[monthnumberofyear] )
<= MONTH ( TODAY () ) - 1,
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) )
),
1,
0
),
IF (
SELECTEDVALUE ( Sheet1[Month] )
IN CALCULATETABLE (
DISTINCT ( Sheet1[Month] ),
FILTER ( ALL ( Sheet1 ), [Year] = SELECTEDVALUE ( 'Table'[year] ) - 1 )
),
1,
0
)
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft ,
Requirement : Always show last 12 months in chart even if the year is not completed. if the year is not completed then will showcase moths completed from current year and rest of months from previous year months otherwise will showcase respective months by selected year
Ex: Case 1 slected year from slicer is : 2020
displayed months ( 2020 jan,2020 feb,2020 mar,2020 apr,2020 may,2020 jun,2020 jul,2020 aug,2020 sep,2020 oct,
2019 nov,2019 dec).
Case 2 : Selected year is : previous year i.e. 2019,2018,... expect current year (2020)
then showcase respective months by selected year
Case 1 : If current year selected from slicer -- 2020
till now didn't complete full year of 2020 so will showcase last 12 months from previous month of 2020
( 2020 jan,2020 feb2020 mar,2020 apr,2020 may,2020 jun,2020 jul,2020 aug,2020 sep,2020 oct,
2019 nov,2019 dec).
1. If it is Current year (2020) so don't have full year data or 12 months of data then will showcase the current year months from previous month(2020 oct - 2020 Jan) remaing 2 months from previous year.
Ex: If current month is 2020- November , then will always showase 12 months of data from( 2020 jan,2020 feb,2020 mar,2020 apr,2020 may,2020 jun,2020 jul,2020 aug,2020 sep,2020 oct,2019 - nov,2020-dec).
Ex: Lets assume If current month is 2020- Feb, then will always showase 12 months of data from( 2020 - jan ,2019-dec,2019-nov,2010-oct,2019-sep,2019-aug,2019-july,2019-june,2019-may,2019-apr,2019-mar,2019-feb)
2. If it is not current year means 2019 ,2018..2000 anything expect 2020 then we had 12 months of data then will showcase respective year months
Ex: 1.if Select 2019 then will show case 2019 jan ,2019 feb,---2019 dec
2. if Select 2018 then will show case 2018 jan ,2018 feb,---2018 dec
Note 1: In x-axis we should always display month name(Jan , Feb,..Dec) not year & month(jan-2020,feb,2020..) irrespective of year (any year )selected from slicer
Note 2: How can you distinguish between November and December in 2019? Also, 2018, 2019, 2020?
End user will understand based on the selected year .i.e. if the year selected is 2020 (Current year) then they will understand current month is nov so will show case till oct from 2020 values (2020 jan...2020 oct) and rest of nov and dec Values from previous year
If the year is selected previous year (2019,2018,.. <2020(Current year) then they will understand that all the months from selected year.
Case 1:
If we select 2020 then will showcase (2019-Dec,2019-Nov) values but in the x-axis we need to display monthname
(Nov and Dec).
Ex: 2020 is not completed so will showcase dec and nov previous year values.
Case 2 : If we select previous year i.e 2019,2018,2017 then will showcase respective year values with the monthnames of jan,feb,nov,dec.
Link :https://1drv.ms/u/s!Au-aOkl1BoHugk-z9TTq_2CDwmE1?e=JwcLBK
Thanks In Advance
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description,I think you need to create a calculated table to extract years,then create a measure to meet your needs,then use it in filter pane.
Like this:
Measure 2 =
IF (
SELECTEDVALUE ( 'Table'[year] ) = MINX ( ALL ( 'Table' ), [year] ),
IF(SELECTEDVALUE ( 'Table'[year] ) = SELECTEDVALUE ( Sheet1[year] ),1,0),
IF (
SELECTEDVALUE ( Sheet1[monthnumberofyear] ) <= 10,
IF ( SELECTEDVALUE ( 'Table'[year] ) = SELECTEDVALUE ( Sheet1[year] ), 1, 0 ),
IF (
SELECTEDVALUE ( 'Table'[year] )-1
= SELECTEDVALUE ( Sheet1[year] ),
1,
0
)
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Janey,
Its working for 2020. But few things need to resolve which are listed below
1. When i select 2019 then its showing currently till 2019 oct like below but expected is to showcase all the months in 2019
Currently Showing in pbix file
Expected Output:
2. When i change in X- axis from Year & month to Month then column chart doesn't show anything. Second graph in attached pbix file
Here are the formulas
Month = FORMAT(Sheet1[Date],"MMM")
Year&Month = CONCATENATE(YEAR(Sheet1[Date]),CONCATENATE("-",FORMAT(Sheet1[Date],"MMM")))
Here is the pbis file : https://1drv.ms/u/s!Au-aOkl1BoHugk-z9TTq_2CDwmE1?e=WWzFeb
Thanks In advance,
Sid
Hi, @Anonymous
Your calculation logic is not consistent,in the previous sample, 2019 is the smallest, so what I did is to display all the months of the year when the year is the smallest, and the others are not.
The data year of the sample data you updated is different from the previous one. 2019 is not the smallest one, so the result has changed.
You can try like this:
Measure 2 =
IF (
SELECTEDVALUE ( 'Table'[year] ) =2019,
IF(SELECTEDVALUE ( 'Table'[year] ) = SELECTEDVALUE ( Sheet1[year] ),1,0),
IF (
SELECTEDVALUE ( Sheet1[monthnumberofyear] ) <= MONTH(TODAY())-1,
IF ( SELECTEDVALUE ( 'Table'[year] ) = SELECTEDVALUE ( Sheet1[year] ), 1, 0 ),
IF (
SELECTEDVALUE ( 'Table'[year] )-1
= SELECTEDVALUE ( Sheet1[year] ),
1,
0
)
)
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft ,
Thanks for that . Currently in x-axis we are showing Year & month format: 2020-Jan,2019-Dec .
In the X- axis , I need to display monthnames : Jan,Feb..dec(
Hi, @Anonymous
When only month is placed on the x-axis, what do you want to display? If you want to display the monthly number of the current year according to the selected year, you can clear the effect of measure on the filter pane.
Like this:
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft ,
When i clear the filter whick you shown like above then it will be dispaly repective months but i wanted to showcase nov and dec values from 2019when you select 2020 from slicer.
When i select 2020 currently its showing like below. When i select 2020 (Any Year) then we need to show case x-axis names as month names. When i change to monthnames it doesn't show anything.
Ex: If i select 2020 then will showcase on x-axis monthnames (nov and dec values from 2019, rest of values from 2020 )
Current Output
Expected Output:
Thanks
Sid
Hi, @Anonymous
There are three years on the slicer. You only talked about the situation when you chose 2020. How to deal with other situations, choose 2018 and 2019? Can you make it clear? Moreover, when you choose 2020, as long as the month is displayed, how can you distinguish between November and December in 2019? Also, 2018, 2019, 2020, either only display the month or only the year and month, there must be a unified logic. Please give me a complete logic,so I can help you soon!Thank you very much.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do you have sample file which we could help with? Thanks!
Hi @alex,
This is not working as per my requirement.
When i Select 2020 in the slicer below is the expected output
When i select 2019 then below is the my expected output
It's best to use a relative date slicer instead of what you're trying to do. It's doable but it's a lot of hassle (with changes to the data model) and you certainly don't want to go this route. By the way, if you select 2019, which months would you like to see in which year?
Hi @daxer-almighty ,
If we select previous years (2019,2018....) then will show 12 months means from 2019 jan,2019 feb..2019 Dec.
Relative is not working for my requirement. Can you guide me how can i achieve this ?
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |