Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Data selection for latest 'available' month

Hi Community,

 

I have a data file that contains historical data and gets updated every month.

The data is running on average 2 months late, but its not fixed either.

Say today in July I have got latest data for May.

Now, in my report I want to put a filter to get the data for latest month only which is May.

I have tried putting the relative filtering but that gives values for current or last month which becomes June.

How can I put a filter which takes and provides values for latest month which is 2 month old data.

Thanks

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    Please add the ALL filter in your measure, like the following formula.

    latest month=CALCULATE(MONTH(LASTDATE(Table[Date])),ALL(Table))


    Best Regards,
    Angelia

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Yup I started off with a lastdate function.

      I am using this to get the latest date, then from that date, I pull the month number information.

      what next. 

      month number is now a measure. and my actual dates are my date table. my intension is to display data for this specific month, so the month number I have just got, need to go into the page level filter, which I cant do. 

      what I am looking at is, somehow, I put a check, where I compare this month number to the month number in my date table, and put that check = TRUE case in page level filter but I cant make that check work. :(

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

        You can create a calculated column in your date table using the formula:

        compare=IF(DateTable[month]=measure,"True","False")


        Then add the calculated column in the page filter level, and check if it works fine.

        Best Regards,
        Angelia