Forum Discussion

vinodDrinkPak's avatar
vinodDrinkPak
Helper II
2 years ago
Solved

Line graph 6 months backward based on Dropdown selection

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

 

Need Help
Show and Tell
  • vinodDrinkPak's avatar
    vinodDrinkPak
    2 years ago

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

11 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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's avatar
      vinodDrinkPak
      Helper II
      2 years ago

      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's avatar
        vinodDrinkPak
        Helper II
        2 years ago

        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

         

  • vinodDrinkPak's avatar
    vinodDrinkPak
    Helper II
    2 years ago

    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