cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Current Month inside Calculation

I have some data that I calculate the average for but I only need to display for the current month. I have a standard date table connected to the data table.  Some sample data with result look like this:

The code I am using with some comments about how I think it is working:

```Avg Amnt = CALCULATE(
CALCULATE(  //Filter the data to just AU currency before Average Calc
SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")),
//get the previous 3 months data excluding current month
DATESBETWEEN('Date'[Date],
DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
)
//Add this for 3 mths average
)/3```

But I know DAX may not being working the way I think it is.

I tried to wrap another Calculate around the output and filter for just the Current Month by adding from the Date Table ,FILTER('Date', 'Date'[Month #] = 9)) but I get an odd result.

Thanks Tex

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Current Month inside Calculation

I created a couple of variables then added an IF statement to determine which variable to display.

```Avg Amnt =
VAR Future_Frcst = CALCULATE(
CALCULATE(AVERAGEX( DATESBETWEEN('Date'[Date],
DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
),CALCULATE(  //Filter the data to just AU currency before Average Calc
SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU"))
)
,FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY()))
),DATESYTD('Date'[Date],"31/3"))

Var Curr_Mth = CALCULATE(
CALCULATE(CALCULATE( SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU"))
))/3/'Date'[DaysinCurrentMonth]*'Date'[DaysLeftCurrentMonth]

Return CALCULATE(if('Date'[YearMonth] < CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00"))
, BLANK()
, if('Date'[YearMonth] = CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00"))
,Curr_Mth
,Future_Frcst
)
))```

Seems to be wokring fine

3 REPLIES 3
Community Support Team

## Re: Current Month inside Calculation

Hi

So your measure is like below, right?

```Avg Amnt =
CALCULATE (
CALCULATE (
//Filter the data to just AU currency before Average Calc
SUMX (
'Sample',
'Sample'[Amount]
),
FILTER ( ALL ( 'Sample'[Currency] ), 'Sample'[Currency] = "AU" )
),
//get the previous 3 months data excluding current month
DATESBETWEEN (
'Date'[Date],
DATEADD ( FIRSTDATE ( 'Date'[Date] ), -3, MONTH ),
DATEADD ( LASTDATE ( 'Date'[Date] ), -1, MONTH )
),
FILTER ( 'Date', 'Date'[Month #] = 9 )
)
/ 3
```

Could you share your raw data, not the table chart, for further analysis?

Regards,

Jimmy Tao

Frequent Visitor

## Re: Current Month inside Calculation

Thanks for looking at this Jimmy.

 Date Amount Currency MthSum Prev3MthAvge CurrentMth/Daysleft 1/04/2018 10 AU 37 80.33333 2/04/2018 15 AU 3/04/2018 12 AU 1/05/2018 20 AU 79 131.6667 2/05/2018 23 AU 3/05/2018 19 AU 4/05/2018 17 AU 1/06/2018 31 AU 125 169 2/06/2018 34 AU 3/06/2018 32 AU 4/06/2018 28 AU 1/07/2018 43 AU 191 2/07/2018 42 AU 3/07/2018 46 AU 4/07/2018 22 AU 5/07/2018 38 AU 1/08/2019 53 AU 191 2/08/2018 58 AU 3/08/2018 33 AU 4/08/2018 47 AU 1/09/2018 76 AU 2/09/2018 65 AU 3/09/2018 33 AU 56.33 1/04/2018 12 US 2/04/2018 17 US 3/04/2018 14 US 1/05/2018 22 US 2/05/2018 25 US 3/05/2018 21 US 4/05/2018 19 US 1/06/2018 33 US 2/06/2018 36 US 3/06/2018 34 US 4/06/2018 30 US 1/07/2018 45 US 2/07/2018 44 US 3/07/2018 48 US 4/07/2018 24 US 5/07/2018 40 US 1/08/2019 55 US 2/08/2018 60 US 3/08/2018 35 US 4/08/2018 49 US 1/09/2018 78 US 2/09/2018 67 US 3/09/2018 35 US

Hopefully this helps.

Frequent Visitor

## Re: Current Month inside Calculation

I created a couple of variables then added an IF statement to determine which variable to display.

```Avg Amnt =
VAR Future_Frcst = CALCULATE(
CALCULATE(AVERAGEX( DATESBETWEEN('Date'[Date],
DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
),CALCULATE(  //Filter the data to just AU currency before Average Calc
SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU"))
)
,FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY()))
),DATESYTD('Date'[Date],"31/3"))

Var Curr_Mth = CALCULATE(
CALCULATE(CALCULATE( SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU"))
))/3/'Date'[DaysinCurrentMonth]*'Date'[DaysLeftCurrentMonth]

Return CALCULATE(if('Date'[YearMonth] < CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00"))
, BLANK()
, if('Date'[YearMonth] = CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00"))
,Curr_Mth
,Future_Frcst
)
))```

Seems to be wokring fine

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 226 members 2,714 guests
Recent signins: