Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone
I hope you can help me with this. I have 2 tables, each one with a Date column (dd-mmm-yyyy) and Quantity, both tables are related with my CalendarTable. I have a filter to select the month and year always from Table1 and I want that after selecting month-year in the filter, the system calculate the average of the quantity of 6 months: 3 months (previous) from Table1 and 2 months (after) of the Table2 in order to get 6 months in total.
I've used @DATESINPERIOD function but I don't know how to reference for both table.
Graphycally I want this:
Thanks in advance,
Raitup00
Solved! Go to Solution.
Hi, @Raitup00
You can try the following methods.
Table:
Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
Year = YEAR([Date])
Month = MONTH([Date])
The relationship between the three tables is shown in the figure, and the filter also uses the calendar table.
Column:
Year month = YEAR([T1_Date])&"-"&MONTH([T1_Date])
Average 1 =
CALCULATE(AVERAGE('Table 1'[T1_Qty]),FILTER('Table 1',[Year month]=EARLIER('Table 1'[Year month])))
Average 2 =
CALCULATE(AVERAGE('Table 2'[T2_Qty]),FILTER('Table 2',[Year month]=EARLIER('Table 2'[Year month])))
Measure:
3 months (previous) =
CALCULATE (
SUMX (
SUMMARIZE ( 'Table 1', 'Table 1'[Average 1], 'Table 1'[Year month] ),
[Average 1]
),
FILTER (
ALL ( 'Table 1' ),
[T1_Date]
>= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) - 3, 1 )
&& [T1_Date]
<= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 ) - 1
)
)
2 months (after) =
CALCULATE (
SUMX (
SUMMARIZE ( 'Table 2', 'Table 2'[Average 2], 'Table 2'[Year month] ),
[Average 2]
),
FILTER (
ALL ( 'Table 2' ),
[T2_Date]
>= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 )
&& [T2_Date]
<= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 3, 1 ) - 1
)
)
Result = DIVIDE([3 months (previous)]+[2 months (after)],6)
Please refer to the attachment for detailed steps.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Raitup00 , Based on what I got.
Rolling 6 = CALCULATE(sum(Table2[Value]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),1),-3,MONTH)) + CALCULATE(sum(Table1[Value]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-2),-3,MONTH))
Thanks @amitchandak for your time
Unfortunatly the option you gave is not what I'm dealing with. Actuually I don´t need a "Rolling" average. I need that DAX calculates the average per month and the month selected must be as a switch between Tables. For example, if I select May-2022, DAX should be able to calcule the Average for
Feb-2022 (-3 month - Table1),
Mar-2022 (-2 month - Table1),
Apr-2022 (-1 month - Table1),
May-2022 (selected month - Table1),
Jun-2023 (+1 month - Table2),
Jul-2023 (+2 month - Table 2)
I hope you can help me again.
Thank in advance
Hi, @Raitup00
You can try the following methods.
Table:
Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
Year = YEAR([Date])
Month = MONTH([Date])
The relationship between the three tables is shown in the figure, and the filter also uses the calendar table.
Column:
Year month = YEAR([T1_Date])&"-"&MONTH([T1_Date])
Average 1 =
CALCULATE(AVERAGE('Table 1'[T1_Qty]),FILTER('Table 1',[Year month]=EARLIER('Table 1'[Year month])))
Average 2 =
CALCULATE(AVERAGE('Table 2'[T2_Qty]),FILTER('Table 2',[Year month]=EARLIER('Table 2'[Year month])))
Measure:
3 months (previous) =
CALCULATE (
SUMX (
SUMMARIZE ( 'Table 1', 'Table 1'[Average 1], 'Table 1'[Year month] ),
[Average 1]
),
FILTER (
ALL ( 'Table 1' ),
[T1_Date]
>= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) - 3, 1 )
&& [T1_Date]
<= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 ) - 1
)
)
2 months (after) =
CALCULATE (
SUMX (
SUMMARIZE ( 'Table 2', 'Table 2'[Average 2], 'Table 2'[Year month] ),
[Average 2]
),
FILTER (
ALL ( 'Table 2' ),
[T2_Date]
>= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 1, 1 )
&& [T2_Date]
<= DATE ( SELECTEDVALUE ( 'Calendar'[Year] ), SELECTEDVALUE ( 'Calendar'[Month] ) + 3, 1 ) - 1
)
)
Result = DIVIDE([3 months (previous)]+[2 months (after)],6)
Please refer to the attachment for detailed steps.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |