Line graph 6 months backward based on Dropdown selection

[vinodDrinkPak]

Hi Team,

I have created 2 line graphs, which is like last 6 months and 6 weeks view and also a dropdown for interaction with this line graph, see below

what i want is when i select the dropdown let say End of the month dropdown , lets say august 31st, it turns the graph like below

ideally what i want is when i select August 31st from the dropdown, i need the line grpah to show me August 31st and 6 months backward trend?

here is my pbix, appreciate any help

https://drive.google.com/file/d/1VKiy7yCzY7Aai3mHvctIHH5ITO4cOKQ6/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in the attached PBI file.

Hope this helps.

[vinodDrinkPak]

@danextian  thanks, could you check how i can interact this dropdowns to my Cards and graph, its available, in the Pbix, i think we need to apply some creative DAX

i am not abe to find a work around, given that we have now disconnected tables for End of week and End of month

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in the attached PBI file.

Hope this helps.

[danextian]

Hi @vinodDrinkPak ,

You need to create disconnected dates table to reference in your measure. If you don't, the visual will show the dates within the range selected from the slicer. The disconnected dates table doesn't have a relationship to the fact table and can be created either in DAX,  M or enter data.  You will then need to create a measure that references the date selected from the disconnected table.
Sample measure:

Inbound2 = 
VAR __END_DATE =
    MAX ( 'Dates(Disconnected)'[End of Month] )
VAR __START_DATE =
    EDATE ( __END_DATE, - 6 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),
        FILTER (
            RECEIPT_CONTAINER,
            RECEIPT_CONTAINER[End of Month] >= __START_DATE
                && RECEIPT_CONTAINER[End of Month] <= __END_DATE
        )
    )

Please refer to the attached pbix.

[vinodDrinkPak]

@danextian 

its so cool, can you help me with the End of Week dropdown as well. thank so much, you are a savior

looks like its broken the dropdown

[vinodDrinkPak]

@danextian  i replicated your formula for End of week 

Inbound Week =

VAR __END_DATE =

    MAX ( 'Dates(Disconnected)W'[End of Week])

VAR __START_DATE =

    EDATE ( __END_DATE, - 6 )

RETURN

    CALCULATE (

        DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),

        FILTER (

            RECEIPT_CONTAINER,

            RECEIPT_CONTAINER[End of Week] >= __START_DATE

                && RECEIPT_CONTAINER[End of Week] <= __END_DATE

        )

    )

the problem is it doesn't restrict the view to last 6 weeks, rather it shows up everything

[danextian]

Hi @vinodDrinkPak 

EDATE function is for adding to or subracting x months from a date. The variable below is equivalent to selected date - 6 months.

VAR __START_DATE =
    EDATE ( __END_DATE, - 6 )

Use this instead

VAR __START_DATE =
     __END_DATE - ( 6  * 7 )

[vinodDrinkPak]

@danextian  thanks for the response

it doesn't work still, see below, i have used this dax

Inbound Week =

VAR __END_DATE =

    MAX ( 'Dates(Disconnected)W'[End of Week])

VAR __START_DATE =

    EDATE ( __END_DATE, - 6*7 )

RETURN

    CALCULATE (

        DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),

        FILTER (

            RECEIPT_CONTAINER,

            RECEIPT_CONTAINER[End of Week] >= __START_DATE

                && RECEIPT_CONTAINER[End of Week] <= __END_DATE

        )

    )

could you please take a look at it, when you get sometime, there is another problem to the measure we are using, i think it doesn't interal with my graphs and cards, pls check

https://drive.google.com/file/d/1EP3BMNtm3ueuFrl0V9a_S5QB_tDXWas6/view?usp=sharing

[danextian]

Hi @vinodDrinkPak ,

This was my suggestion as the variable:

VAR __START_DATE =
     __END_DATE - ( 6  * 7 )

but you wrote:

VAR __START_DATE =
    EDATE ( __END_DATE, - 6*7 )

[vinodDrinkPak]

@danextian  i think this formulas are not interacting with my cards and other visuals? do i need to make some tweak, i am aware the slicer you have used is coming from disconnected table

earliers it was from the Receipt container tables and was working with all visuals, now given that we have brought in disconnected tables, i am not able to interact with other visuals, as its only interacting with Line graphs

any solution you could share?

[vinodDrinkPak]

@danextian 

hi, are you able to support or share some idea to help?

Otherwise these disconnected tables will not help, it may only help for the line graph, not the visuals on my dashboard

here is my pbix file, i have been looking for solution since  last 2 days, any help will be highly appreciated

https://drive.google.com/file/d/1EP3BMNtm3ueuFrl0V9a_S5QB_tDXWas6/view?usp=sharing

[vinodDrinkPak · Accepted solution]

@danextian 

pls ignore i manage to find a solution

i created another measure

Inbound =

VAR __END_DATE_WEEK =

    MAXX(ALLSELECTED('Dates(Disconnected)W'),'Dates(Disconnected)W'[End of Week])

VAR __END_DATE_MONTH =

    MAXX(ALLSELECTED('Dates(Disconnected)M'),'Dates(Disconnected)M'[End of Month])

RETURN

    IF(ISFILTERED('Dates(Disconnected)W'[End of Week]),

        CALCULATE(

            DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),

            FILTER(

                RECEIPT_CONTAINER,

                RECEIPT_CONTAINER[End of Week] = __END_DATE_WEEK

            )

        ),

        IF(ISFILTERED('Dates(Disconnected)M'[End of Month]),

            CALCULATE(

                DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),

                FILTER(

                    RECEIPT_CONTAINER,

                    RECEIPT_CONTAINER[End of Month] = __END_DATE_MONTH

                )

            ),

            DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID])

        )

    )