cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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:

 Year Month Current month value Previous Month value Current - Previous 2021 January 100 0 100 2021 February 200 100 100 2021 March 300 200 100 2021 April 400 300 100 2021 May 500 400 100 2021 June 600 500 100 2021 July 700 600 100 2021 August 800 700 100 2021 September 900 800 100 2021 October 1000 900 100 2021 November 1100 1000 100 2021 December 1200 1100 100

BR

PP

1 ACCEPTED SOLUTION
Super User

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

3 REPLIES 3
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

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

Helper II

@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))
Super User

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