Previous Month Calculated Column not working for January

[ClaireBear]

Hello, 

I have a calculated column that flags previous month entries. The issue im having is that now in January, the calculation does not seem to work. Last year this was working beautifully. 

Example: 

IsPreviousMonth = IF (

YEAR ( 'Fact Sales'[TransactionDateTime].[Date]) = YEAR (TODAY ())

&& MONTH( 'Fact Sales'[TransactionDateTime].[Date]) = MONTH(TODAY () -30 ),

"Yes",

"No"

)

Secondly, i am trying to apply the same method to a quarter but have no luck. If previous quarter then"Yes" else "No"

Reason for all this - I am trying to create cards next to the graphs.

 

Example of Cards

Any suggestions would be great!

[amitchandak]

If you have a date table you can use time intelligence functions

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Gordonlilj]

Hi,

I'm not sure if this is what you're after but you could try and change the code to something like below

IsPreviousMonth = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 MONTH( 'Fact Sales'[TransactionDateTime].[Date]) = 
 FORMAT(DATE( YEAR(TODAY()),
              MONTH(TODAY())-1,DAY(
              TODAY())),"YYYYM"),
"Yes",
"No"
)

[ClaireBear]

Yes, thank you. It is working. 

I now have to figure out how to do this for Quarters.

[Gordonlilj]

If its the previous quarter you're after then you could probably replace MONTH with QUARTER like below

IsPreviousQuarter = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 QUARTER( 'Fact Sales'[TransactionDateTime].[Date]) = 
  FORMAT(DATE( YEAR(TODAY()),
               QUARTER(TODAY())-1,
               DAY(TODAY())),"YYYYQ"),
"Yes",
"No"
)

[ClaireBear]

Thank you! Unfortunately there is no calculation for Quarter. 

[Gordonlilj · Accepted solution]

The Quarter function came with the 2019 december update.

You could use Roundup instead

IsPreviousQuarter = 

IF (
 YEAR ('Fact Sales'[TransactionDateTime].[Date]) & 
 ROUNDUP(MONTH('Fact Sales'[TransactionDateTime].[Date])/3,0) = 
  FORMAT(DATE( YEAR(TODAY()),
               ROUNDUP(MONTH(TODAY())/3,0)-1,
               DAY(TODAY())),"YYYYQ"),
"Yes",
"No"
)

[ClaireBear]

Thank you so much! You have solved my issue.