Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Display data for previous month with Month-Year filter

I have 2 visuals that interact with my Month-Year Filter (Format MMMM YYYY):

Date_No_Duplicate = FORMAT(DATE(YEAR([CreatedDate]), MONTH([CreatedDate]), 1), "MMMM YYYY")


The 1st visual shows the selected month's data, and the 2nd visual is supposed to show the previous month's data of the selected month in filter. Each visual uses 3 measures in Y-axis.



I also have another Month-Year Filter (called Period) for the whole dashboard so I created a measure as a filter. I have included this within an IF condition in my measures so it will check if the month selected is also selected in Period filter.

SelectedPeriod =
VAR SelectedPeriodValues = VALUES('Table'[CreatedDate])
RETURN
IF (
    SELECTEDVALUE('Table'[Date_No_Duplicate]) IN SelectedPeriodValues,
    1,
    0
)

As for my issue in the 2nd visual (previous month), I've tried converting my date filter back to date type then using it (tried other date columns) as a filter by setting the relative date to the last 1 calendar month, but it's being set to May 1 - May 31 statically so it always results to no data. I also tried to create a separate measure to be used as a filter but, no luck.

Any other solutions?






Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I was able to fix it. This worked for me:

    PreviousPeriod =
    VAR PeriodValues=
        CALCULATETABLE(VALUES('Table'[CreatedDate]), REMOVEFILTERS(DimDate))
    VAR PreviousMonth= EDATE(SELECTEDVALUE('DimDate'[Month Year]), -1)

    RETURN
    IF (
        PreviousMonth IN PeriodValues,
        1,
        0
    )
    Then used it in my measures with an IF condition equals to 1. I also added
    REMOVEFILTERS(DimDate) in my count measures included in the calculation.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I was able to fix it. This worked for me:

    PreviousPeriod =
    VAR PeriodValues=
        CALCULATETABLE(VALUES('Table'[CreatedDate]), REMOVEFILTERS(DimDate))
    VAR PreviousMonth= EDATE(SELECTEDVALUE('DimDate'[Month Year]), -1)

    RETURN
    IF (
        PreviousMonth IN PeriodValues,
        1,
        0
    )
    Then used it in my measures with an IF condition equals to 1. I also added
    REMOVEFILTERS(DimDate) in my count measures included in the calculation.
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      Congratulations on finding the appropriate solution through your own efforts, and I'm sure your thinking will be able to help other users on the forum.

       

       

      Best Regards

      Yilong Zhou

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    We can use a date table and create a measure to meet your requirement.

    1. Create a date table based on your date, there is no relationship between two tables.

    Date =
ADDCOLUMNS (
    CALENDAR ( "2019/7/1", "2020/6/1" ),
    "year", YEAR ( [Date] ),
    "Month_name", FORMAT ( [Date], "mmm" ),
    "Month_number", MONTH ( [Date] )
)

    2. Create a measure like this.

    Measure =
VAR selected_ =
    SELECTEDVALUE ( 'Date'[Month_name] )
VAR Date_ =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER ( 'Date', 'Date'[Month_name] = selected_ )
    )
RETURN
    IF (
        ISBLANK ( selected_ ),
        CALCULATE ( SUM ( 'Table'[Values] ) ),
        CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER ( 'Table', 'Table'[Date] <= Date_ )
        )
    )

    3. At last we need to add a month slicer based on Date[Month_name], and the result like this.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous,

      The right graph for the selected month-year's data is working properly. Here's the measure used for the light blue one (and there are 2 other measures for the remaining percentages that work as expected):

      RightGraph =
      IF (
      [SelectedPeriod] = 1,
      DIVIDE(
      300 - ([CountCondition1] + [CountCondition2]),
      300,
      0
      ),
      0
      )
       And the SelectedPeriod measure, where CreatedDate column is used as a slicer for the whole dashboard with multiple selection (Month Year format), and Date_No_Duplicate is also a slicer with single selection (Month Year format) to filter the 2 graphs that I've provided.
      SelectedPeriod =
      VAR SelectedPeriodValues = VALUES('Table'[CreatedDate])
      RETURN
      IF (
      SELECTEDVALUE('Table'[Date_No_Duplicate]) IN SelectedPeriodValues,
      1,
      0
      )

       

       


      As a solution, I tried this tutorial until 4:22 for the static filter: https://www.youtube.com/watch?v=CaFpeY1lPTI where DATESINPERIOD function was used. I used DimDate[Month Year] as a slicer and I modified the measure to display the previous month's data instead of the last 6 months:
      LeftGraph =

      VAR MaxDate = MAX('DimDate'[Date])
      VAR PrevMonth = DATESINPERIOD('New Date'[Date], MaxDate, -1, MONTH)

      RETURN

      CALCULATE(

      DIVIDE(300 - ([CountCondition1] + [CountCondition2]),
      300,
      0
      ),


      REMOVEFILTERS(DimDate),
      KEEPFILTERS(PrevMonth),
      USERELATIONSHIP('DimDate'[Date], 'New Date'[Date]) -- Inactive

      )


      Unfortunately, it still did not work, because I checked which months are included in the graph by dragging the Month Year in the X-axis, and it returns ALL:

      The right graph is affected, did the same thing with the X-axis, and it's still displaying correct values for a single month. So I guess the only problem here is either my filter or measure for the left graph to display previous month's data.