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 All,
I have a huge data base that has rows for every calendar date (almost 2 years of data) . I need the data of one calendar date in one column and other calendar date in other column while importing. so i can calculate the increase/decrease between two different dates.
Any suggestions on how to di it?
Currently I am able to filter rows based on only one date using this
= Table.SelectRows(#"Removed Other Columns", each [calendar_date] = Date.From(Date.AddDays( DateTime.LocalNow(),-1)))
Solved! Go to Solution.
You want to calculate the increase/decrease between which two different dates? Every date and its previous date? If so, you can first duplicate the query.
Then in the duplicated table, add a custom column to get the previous date.
Date.AddDays([Date],-1)
Then merge queries to bring data from the original table into the duplicated table. Hold on Ctrl key and select "Previous Date" & "Item" columns as matching columns in duplicated table. In the second table, select "Date" and "Item" columns.
Expand the merged table column and select "Inventory" to add to the duplicated table.
You can then calculate the difference between two inventory columns to get the daily decrease/increase amount.
If you don't want to bring two tables' data into the data model, you can uncheck "Enable load" option for the original table.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
@santoshreddyk95 , Best is use a measure and time intelligence
example
This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))
diff =[This Day] - [Last Day]
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
Each Calendar day has 1000+ rows which are distinct. Kind of Inventory details. For every row item i need a difference. Increase in inventory for every material.
You want to calculate the increase/decrease between which two different dates? Every date and its previous date? If so, you can first duplicate the query.
Then in the duplicated table, add a custom column to get the previous date.
Date.AddDays([Date],-1)
Then merge queries to bring data from the original table into the duplicated table. Hold on Ctrl key and select "Previous Date" & "Item" columns as matching columns in duplicated table. In the second table, select "Date" and "Item" columns.
Expand the merged table column and select "Inventory" to add to the duplicated table.
You can then calculate the difference between two inventory columns to get the daily decrease/increase amount.
If you don't want to bring two tables' data into the data model, you can uncheck "Enable load" option for the original table.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
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 |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |