Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PBIDEV_10
Helper II
Helper II

If condition on month

Hi

I am calculating current month values by current month - previous month values (PREVIOUSMONTH function). But for january month, it is displaying values for december last year which is wrong with respect to my graph. When the month is january then previous month value shold show zero value. How do I get this in formula? For example: 

 

YearMonthCurrent month valuePrevious Month valueCurrent - Previous
2021January1000100
2021February200100100
2021March300200100
2021April400300100
2021May500400100
2021June600500100
2021July700600100
2021August800700100
2021September900800100
2021October1000900100
2021November11001000100
2021December12001100100

 

BR

PP

1 ACCEPTED SOLUTION

@PBIDEV_10 ,  Try a measure like

Measure =

var _min = minx(allselected('Date)', Date[Date])

return 

if(Min('Date'[Date]) =_min , 0,  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) )

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@PBIDEV_10 , Are doing this with help from time intelligence and date table,

 

example

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

 

if needed create a date using month and year

 

Date = datevalue("01-"&[Month] & "-" &[Year])

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

 

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

 

@amitchandak  Yes. I have date table and I am using date from that table for time intelligence functions. My issue is all other previous month values from february comes correct. only for the month of january, I want to show zero value.

New members last month = CALCULATE(SUM([New members]),PARALLELPERIOD(Kalender[Dato],-1,MONTH))

@PBIDEV_10 ,  Try a measure like

Measure =

var _min = minx(allselected('Date)', Date[Date])

return 

if(Min('Date'[Date]) =_min , 0,  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) )

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.