Forum Discussion
Helper IIIf 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
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
- amitchandak
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=6LUBbvcxtKAWhy Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw
PBIDEV_10amitchandak 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))- amitchandak
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))) )