Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tex_01
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
Tex_01
Frequent Visitor

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

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.