Display results of different columns based on current month

[Anonymous]

Hi Guys,

I hope you can help.

I have a pretty database that has the results of financilas in different columns. eg

Attribute 1 |  Attribute 2 | Jan | Feb | Mar | Apr |.....| Nov | Dec|

Based on the current month, I need to display 

* The Last Month (Current Month -1)

* The month before  (Current month -2)

I have created some measures 

one for the current month based on the now()

Then a switch command to see if the current month is x then return x, so I can get a number

But I can work out to use a formula to say

if the current month is 5, then display the column Apr

Can you please assist

[amitchandak]

@Anonymous , did not get it completely. But with time intelligence and date table you can get

Examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-2,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))


Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Hi @amitchandak 

Thank you for you quick reply.

My issue is the months are not all in one column, they are separated.

So instead of having one column named Month and each month is listed in the results,

I have 12 columns, one for each month and each column has the results

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread. If you still need help, please share some sample data and your expected output. Then we will understand clearly and sove it quickly.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I think it may be better to unpivot month columns and then do calculation. For now, you could get the data of last month using if function. However, it is static. When we are in June, the measure is still returning results of April.

• Please unpivot columns firstly.

• And if you have a date column, you could create a calendar table and create the relationship between them.

For more details, there are many blogs that you could reference:

The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI 

Display Last N Months & Selected Month using Single Date Dimension in Power BI 

DAX Fridays #16: DATEADD