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

 

danextian's avatar
danextian
Super User
2 years ago

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

    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

     

     

    • danextian's avatar
      danextian
      Super User
      2 years ago

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

        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?