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.
Hi I have repeted part numbers with different date, need to create a new column by using dax, that having days numbers, difference between current date and earlier date. if part number is recording for the forst time days should be 0, and for next record days would be difference between 2nd record date and 1st record date .
ID | Date | No Of Days |
R123 | 23/09/2023 | 0 |
R123 | 22/10/2023 | 30 |
R123 | 01/11/2023 | 11 |
R234 | 29/09/2023 | 0 |
R234 | 12/10/2023 | 14 |
R234 | 23/11/2023 | 51 |
Hi @ManeeshaY_12345
Please check the steps for your data and update if you are getting the desired results.
Step 1: Here is my custom dataset.
Step 2: I have created a Calculated Column to rank All the products based on the date.
You will have multiple dates for a single ID right?
right now you have only 3 but in future, it will have multiples
Yes, In future new records will add for same ID.
@ManeeshaY_12345 , We can use offset function to get that
Last row = CALCULATE(Sum(Table[Numer of Days]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID])))+0
Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
Thanks @amitchandak for sharing above logic,
but I want to confirm you that I am expecting to create NO of days column. as of now I dont have that column in my table for understanding i just mentioned there.
Sorry for unclear information.
@ManeeshaY_12345 , My Mistake ,Please try
Last row =
var _last =CALCULATE(Max(Table[Date]) , OFFSET(-1, ALLSELECTED('Table'[ID],'Table'[DAte]), ORDERBY('Table'[Date],asc),KEEP,PARTITIONBY('Table'[ID]))), Max('Table'[Date])
return
if(isblank(_last), 0, Datediff(_last, Max('Table'[Date]) , day))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
86 | |
68 | |
64 | |
63 |
User | Count |
---|---|
208 | |
120 | |
113 | |
79 | |
72 |