cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tex_01 Frequent Visitor
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:

SampleDataresult.PNG

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 ),
                                                         DATEADD(LASTDATE ('Date'[Date]),-1,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.

SampleDataCalc.PNG

Thanks Tex

1 ACCEPTED SOLUTION

Accepted Solutions
Tex_01 Frequent Visitor
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 ),
                                                         DATEADD(LASTDATE ('Date'[Date]),-1,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"))  
                                  ),DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) 
                                                ))/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
Community Support Team

Re: Current Month inside Calculation

Hi Tex_01,

 

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

Tex_01 Frequent Visitor
Frequent Visitor

Re: Current Month inside Calculation

Thanks for looking at this Jimmy.

 

DateAmountCurrencyMthSumPrev3MthAvgeCurrentMth/Daysleft
1/04/201810AU3780.33333   
2/04/201815AU   
3/04/201812AU   
1/05/201820AU79131.6667  
2/05/201823AU  
3/05/201819AU  
4/05/201817AU  
1/06/201831AU125169 
2/06/201834AU 
3/06/201832AU 
4/06/201828AU 
1/07/201843AU191 
2/07/201842AU 
3/07/201846AU 
4/07/201822AU 
5/07/201838AU 
1/08/201953AU191  
2/08/201858AU  
3/08/201833AU  
4/08/201847AU  
1/09/201876AU    
2/09/201865AU    
3/09/201833AU   56.33
1/04/201812US     
2/04/201817US     
3/04/201814US     
1/05/201822US     
2/05/201825US     
3/05/201821US     
4/05/201819US     
1/06/201833US     
2/06/201836US     
3/06/201834US     
4/06/201830US     
1/07/201845US     
2/07/201844US     
3/07/201848US     
4/07/201824US     
5/07/201840US     
1/08/201955US     
2/08/201860US     
3/08/201835US     
4/08/201849US     
1/09/201878US     
2/09/201867US     
3/09/201835US     

Hopefully this helps.

Tex_01 Frequent Visitor
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 ),
                                                         DATEADD(LASTDATE ('Date'[Date]),-1,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"))  
                                  ),DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) 
                                                ))/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

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 226 members 2,714 guests
Please welcome our newest community members: