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

       

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