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 

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

 

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