date function

3 Topics

Calculate Past Due Backlog Over Time
I have a data set that includes all line items sold, their due dates, actual ship dates, and value. I am able to calculate today's past due backlog (PDBL) by summing the value of all lines whose due date is before today but whose ship date is blank. What I want to do is to create a table that shows what the PDBL was on each day. I don't know how to do this. How can I create a table of dates and compare each line to the date in the table to say 'count me if my due date is before this day but my ship date is on or after this day'. I don't know how to create a table with dates that are not linked specifically to a date for each line item sold, because items can be counted in multiple days if they are late by multiple days. So if something was due 10/2/22 and shipped 10/4/22 I would want its value counted in both 10/3/22 and 10/4/22 of the table. What date do I use in my output table? Do I have to somehow create a new field? Would I create a measure to reference this new date field and how do I do that? Here is a quick very simplified example set of data and an example of what I want my output to look like: Example Data Order # Order Date Due Date Ship Date Value 1 09/30/22 10/1/22 10/3/22 $5 2 10/1/22 10/2/22 10/3/22 $7 3 10/2/22 10/3/22 10/4/22 $10 4 10/2/22 10/4/22 10/4/22 $5 5 10/3/22 10/4/22 10/5/22 $5 Output Visual: Date Backlog Value Backlog Count 10/02/22 $5 1 10/03/22 $12 2 10/04/22 $10 1 10/05/22 $5 1
Solved
aallman
3 years ago Place DAX Commands and Tips
1.6KViews
0likes
1Comment
Date Function - DAX - Create new column from an existing date column
Hi I'm trying to create following columns using DAX from an existing date column on a table. 1. Fiscal_year_numer : Output -> 2021-22 I've created a "year" column with DAX like below: Year = Year (Reference to the table [specific column consisting dates]) Can anyone suggest how to create a column with a fiscal column output - "2021-22" from a specifc column consisting dates on same table. Any help would be much appreciated. thanks
Anonymous
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
5Comments
Trying to make a formula conditional by date depending on time from current date
Hi all, I am trying to implement the Excel formula in the below screenshots into DAX for use in a Power BI dashboard. Here is my data, the formula, and an explanation of what the formula does: If a date is over 6 days ago, I do not want to adjust its value. If a date is within 6 days of the current date but more than 2 days ago (so 3, 4, 5, or 6 days ago), I want to increase it by 11.5% each day for the number of days remaining until it hits 6 days. If a date was within the past 2 days, I want to increase it by 13% each day for the number of days remaining until it hits 6 days. The formula shown below accomplishes that in Excel. I am now trying to port the formula into Power BI. I used a formula identical to the one above, but I replaced DAYS() with DATEDIFF(). However, I get an error when trying to reference the date column, saying that "A single value for column 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Solved
Anonymous
4 years ago Place DAX Commands and Tips
965Views
0likes
4Comments