Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need one help from you. I am stuck in one logic..
i am trying to get correct dax for rolling average of last 3 months excluding current month
i have tried following dax
Solved! Go to Solution.
Hi @nthalkar ,
You don't have to make one to one relationship, just drag date column in one table to another, it will automatically create relationship. I modify my sample and it works fine.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nthalkar ,
According to your description, I create a sample , you can try like this.
1.Create a calendar table
Table2 =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), TODAY () ),
"YEARMONTH",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
2.Rank it by month.
RANK=RANKX('Table2',[YEARMONTH],,DESC,Dense)
3.Create relationship.
4.Create a measure to calculate the average.
Measure =
CALCULATE (
AVERAGE ( 'Table'[Sales] ),
FILTER ( 'Table2', [RANK] IN { 2, 3, 4 } )
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank You..
I have made same table and put logic as per you shared but i cant make releationship one to one due to this possibly answer is not coming right
demo Excel attached
MONTH | Stockist Code. | Stockist Name. | Material Desc | Division | Plant | Sale Value |
Apr-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 3,016.66 |
Apr-21 | 10013 | LALCO PHARMA (P) LTD. | Cosvate - G Cream 20g Lami tube | 30 | DLHI | 3,645.00 |
Apr-21 | 10013 | LALCO PHARMA (P) LTD. | Amrolstar Nail Lacquer 2.5ml vials | 21 | DLHI | 2,565.00 |
Apr-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next Roll-on 50ml | 21 | DLHI | 1,826.87 |
Jun-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 1,508.33 |
Jun-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next Roll-on 50ml | 21 | DLHI | 1,826.87 |
Jul-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 3,016.66 |
Aug-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 3,016.66 |
Sep-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 4,524.99 |
Sep-21 | 10013 | LALCO PHARMA (P) LTD. | Amrolstar Nail Lacquer 2.5ml vials | 21 | DLHI | 1,410.07 |
Oct-21 | 10013 | LALCO PHARMA (P) LTD. | Cosmelite Next cream 30g | 21 | DLHI | 9,643.11 |
Oct-21 | 10013 | LALCO PHARMA (P) LTD. | Amrolstar Nail Lacquer 2.5ml vials | 21 | DLHI | 2,820.15 |
Hi @nthalkar ,
You don't have to make one to one relationship, just drag date column in one table to another, it will automatically create relationship. I modify my sample and it works fine.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi All, I found the Rolling Average in "quick Measure" of PBI is not correct. There are more than 1 way to create the rolling average DAX, that we could find from different websites. Below one is what I am using. For your reference:
Step 1, you need a Date table (e.g.: 'Calendar'[Date]). Also can be found via different website.
Step 2, Create a Measure for your desired value. E.g.:
Actual =
Step 3, Create a Measure for rolling average as below:
@nthalkar Should be able to use a slightly modified rolling months:
Rolling Months - Microsoft Power BI Community
Thank You Greg..
Can you suggest me if any correction or modification in my Dax
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |