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

Need to Show last 12 months from previous month based on slicer selection

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

vasu538_0-1605457882865.png

 

Expected Output

 

vasu538_1-1605458866366.png

 

Thanks In Advance

Siddanth.

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi, @vasu538 

 

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
        )
    )
)

15.png

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.

View solution in original post

15 REPLIES 15
Microsoft
Microsoft

Hi, @vasu538 

 

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
        )
    )
)

15.png

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.

View solution in original post

Helper III
Helper III

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

Microsoft
Microsoft

Hi, @vasu538 

 

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
        )
    )
)

3.png

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

vasu538_0-1605724086959.png

Expected Output:

vasu538_1-1605724173778.png

 

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, @vasu538 

 

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(

FORMAT(PCB[Date],"MMM")) but when i place the monthname in the x-axis then it doesn't show anything.
 
 
Thanks,
Sid.

 

Hi, @vasu538 

 

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:

1.png

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

 

vasu538_1-1606145731110.png

 

 

Expected Output:

 

vasu538_2-1606145912160.png

Thanks 

Sid

 

Hi, @vasu538 

 

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.

Resolver I
Resolver I

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 

vasu538_0-1605605653515.png

 

When i select 2019 then below is the my expected output

 

vasu538_1-1605605764025.png

 

 

Post Prodigy
Post Prodigy

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 ?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors