DAX measure to apply same value to each month

Question

I'm trying to consolidate a Year End projection that I got working using multiple measure. I'm running into an issue where I'm trying to get September's value to show in all months.

Note This is not the completed code.

This worked perfectly for my CompMoCI variable. I can't get it to work for my HisCompMoAvg variable.

SAIFI YEP = 
var CurYr = YEAR(TODAY())
var CurMo = MONTH(TODAY())
var Yr1Back = CurYr - 1
var Yr2Back = CurYr - 2
var Yr3Back = CurYr - 3
var Yr4Back = CurYr - 4
var Yr5Back = CurYr - 5
var CurYrCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = CurYr)
var CompMoCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = CurYr, 'Date Table'[Monthnumber] = (CurMo-1), ALLSELECTED(Outages), ALLSELECTED('Date Table'), ALLSELECTED(Customers))
var Yr1BackCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = Yr1Back)
var Yr2BackCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = Yr2Back)
var Yr3BackCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = Yr3Back)
var Yr4BackCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = Yr4Back)
var Yr5BackCI = CALCULATE(TOTALYTD([CI by Data Naming Convention], 'Date Table'[Date]), 'Year'[Year] = Yr5Back)
var HisTotal = CALCULATE(Yr1BackCI + Yr2BackCI + Yr3BackCI + Yr4BackCI + Yr5BackCI, ALLSELECTED(Outages), ALLSELECTED('Date Table'[Date]), ALLSELECTED(Customers))
var HisTotalAvg = CALCULATE(HisTotal, ALLSELECTED(Outages), ALLSELECTED('Date Table'), ALLSELECTED(Customers))/5
var AvgMoAhead = if(MAX('Date Table'[Monthnumber]) >= CurMo, HisTotalAvg, 0)
var HisCompMoAvg = CALCULATE(HisTotalAvg, 'Date Table'[Monthnumber] = 9, ALLSELECTED(Outages), ALLSELECTED('Date Table'), ALLSELECTED(Customers))
var test = HisCompMoAvg
return test

Please read that https://stackoverflow.com/help/minimal-reproducible-example and leave only necessary code to reproduce your issue. Also think of desired results to show us direction. — Przemyslaw Remin, Oct 24 '19 at 15:07
So I'm getting something like this for HisCompMoAvg; Jan = 13, Feb = 32, Mar = 45, Apr = 61, May = 77, June = 85, July = 98, Aug = 109, Sept = 130, Oct = 151, Nov = 178, & Dec = 196. I would like to see; Jan = 130, Feb = 130, Mar = 130, Apr = 130, May = 130, June = 130, July = 130, Aug = 130, Sept = 130, Oct = 130, Nov = 130, & Dec = 130. — akaballer8, Oct 24 '19 at 15:35
Please reword your question. Start from initial data you have put in your comment. Then desired results. Explain how you get 130 from your initial data. I could not guess it. It is not an average. — Przemyslaw Remin, Oct 25 '19 at 08:09

score 0 · Answer 1 · answered Oct 25 '19 at 10:40

This question should be reworded. We can only guess what you want.

+-------+-------+-----------------+
| month | sales | expected result |
+-------+-------+-----------------+
| Jan   |    13 |             130 |
| Feb   |    32 |             130 |
| Mar   |    45 |             130 |
| Apr   |    61 |             130 |
| May   |    77 |             130 |
| Jun   |    85 |             130 |
| Jul   |    98 |             130 |
| Aug   |   109 |             130 |
| Sep*  |   130 |             130 |
| Oct   |   151 |             130 |
| Nov   |   178 |             130 |
| Dec   |   196 |             130 |
+-------+-------+-----------------+

Question. How to construct a DAX measure which shows September sales for all the months?

!Sales = SUM('Table'[sales])

YourMeasure = CALCULATE([!Sales], ALL('Table'[month]), 'Table'[month]="Sep")

Read about functions ALL, CALCULATE, and KEEPFILTERS.

DAX Calculate function with and without FILTER

Difference between CALCULATE(m, x=red) vs CALCULATE(m, KEEPFILTERS(x=red))

DAX Total by category displayed for single category

DAX measure to apply same value to each month

1 Answers1