Sum IF conditional on date

[funklordofusa]

Hi experts!  

I am having trouble writing a measure to give me sum of sales for months that have passed and a run rate for the current month.

The description of what I'm trying to accomplish is:

If month is previous to this month, sum of sales, if month is this month, run rate.  Is there a conventional way to accomplish this?

Thanks for your help everyone!

[MFelix]

Hi @funklordofusa,

This is very low information to help you can you share some sample data and how your model is set up and expected results.

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

Regards,

MFelix

[funklordofusa]

Sure.  First table is the raw data, second is just the sum of raw data and third is the output I'm hoping to show--sum for the 3 completed months but the runrate (separate measure) for April.

date	value		Date	Sum		Date	Sum**
Jan	2		Jan	11		Jan	11
Jan	4		Feb	12		Feb	12
Jan	3		Mar	12		Mar	12
Jan	2		Apr	6		Apr	12
Feb	3
Feb	1
Feb	4
Feb	4
Mar	3
Mar	2
Mar	4
Mar	3
Apr	4
Apr	2

[Anonymous · Accepted solution]

With the month name alone we cannot determine if the month is the current month or not because the same month comes every year. So I took the liberty to change your table structure and put the actuals dates in your date field as shown below...

Table  Name: RawData

Date	value
01-01-2019	2
02-01-2019	4
03-01-2019	3
04-01-2019	2
01-02-2019	3
02-02-2019	1
03-02-2019	4
04-02-2019	4
01-03-2019	3
02-03-2019	2
03-03-2019	4
04-03-2019	3
01-04-2019	4
02-04-2019	2
29-06-2019	5

Now to get the desired results you have to add one calculated column before writing your measure.

Calculated Column : MonthName

MonthName = FORMAT(RawData[Date],"mmm")

Measure

SUM** = IF(
MAX(RawData[Date])< DATE(YEAR(TODAY()),MONTH(TODAY()),1),
SUMX(RawData,RawData[value]),
12)

This will give you the result. Please note that I have written 12 in the measure. You may change that to the formula for calculating the run rate as per your requirement.