cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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: 

 

deanbland_0-1605538753890.png

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

1 ACCEPTED SOLUTION
Resolver II
Resolver II

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

 

Current Month RAG = CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER('Table',MONTH('Table'[Report Date])=MONTH(TODAY())))
Previous Month RAG = 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 shown below

Untitled 3.png

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

 

View solution in original post

4 REPLIES 4
Resolver II
Resolver II

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

 

Current Month RAG = CALCULATE(FIRSTNONBLANK('Table'[RAG Status],TRUE()),FILTER('Table',MONTH('Table'[Report Date])=MONTH(TODAY())))
Previous Month RAG = 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 shown below

Untitled 3.png

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

 

View solution in original post

Works a treat, thank you!

Super User III
Super User III

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

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

 

deanbland_0-1605604980604.png

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors