Forum Discussion

deanbland's avatar
deanbland
Icon for Helper III rankHelper III
5 years ago
Solved

How to create a table that presents current and previous month data.

Hi, 

 

I am trying to create a table that compares the current months DAIs with the previous months. Example given below: 

 

I am wanting the data to actively change - so when it's October the previous months is September and when the Current month turns to November, the previous month will change to October. 

 

Thanks, 

 

Dean

Need Help
  • jaideepnema's avatar
    jaideepnema
    5 years ago

    @deanbland please create the following two measures.... here's the DAX Code

    Mes actual RAG - CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER('Table',MONTH('Table'[Report Date])-MONTH(TODAY())))
    Rag del mes anterior - CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER(ALLEXCEPT('Table','Table'[DAI]),MONTH('Table'[Report Date])-MONTH(TODAY())-1))

    This will give you the required output as below screenshot shown

    Untitled 3.png

    Please accept this as a solution if this resolved your query!! Appeciate kudos also 😀

4 Replies

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    5 years ago

    @deanbland please create the following two measures.... here's the DAX Code

    Mes actual RAG - CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER('Table',MONTH('Table'[Report Date])-MONTH(TODAY())))
    Rag del mes anterior - CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER(ALLEXCEPT('Table','Table'[DAI]),MONTH('Table'[Report Date])-MONTH(TODAY())-1))

    This will give you the required output as below screenshot shown

    Untitled 3.png

    Please accept this as a solution if this resolved your query!! Appeciate kudos also 😀

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Have you tried using DateAdd(...-1,Month)  or EDATE(-1)  in your DAX?

    • deanbland's avatar
      deanbland
      Icon for Helper III rankHelper III
      5 years ago

      Unsure how to action this... I am relitively new to Power BI! 

       

      I have produced a mock-up table below, what would the DAX formula be/ what steps would I need to carry out? 

       

      The ideal scenario would be to add a column that shows as true or false (true when the date is one month ago and false for any other date).