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 ya!
Question: I need to do get a spesific date from fact table that doesn't have all the dates, how do I accomplish this? I do have a calendar table but the max(calendar[date]) doesn't equal max(fact[date]). So I would need to go back in time from the date that is max(fact[date]).
The thing is I got
Thank you all!
Solved! Go to Solution.
First, DATEADD is a Time-intelligence function, so you'd better add a dim date table in your model for calculation.
https://radacad.com/do-you-need-a-date-dimension
Second, for "I'm guessing this is due to fact table missing that date", this means that there is no data that before 6 months of LASTDATE(FACT[PDATE]).
For example: if current row context of LASTDATE(FACT[PDATE]) is 7/19/2019, when there is no data in 1/19/2019, it will return blank.
Third, "how do I get the last date of that month?", you could try this formula
EOMONTH(LASTDATE('FACT'[PDATE]),0)
Regards,
Lin
First, DATEADD is a Time-intelligence function, so you'd better add a dim date table in your model for calculation.
https://radacad.com/do-you-need-a-date-dimension
Second, for "I'm guessing this is due to fact table missing that date", this means that there is no data that before 6 months of LASTDATE(FACT[PDATE]).
For example: if current row context of LASTDATE(FACT[PDATE]) is 7/19/2019, when there is no data in 1/19/2019, it will return blank.
Third, "how do I get the last date of that month?", you could try this formula
EOMONTH(LASTDATE('FACT'[PDATE]),0)
Regards,
Lin
If your date is joined is date table, use date filter on date table. Yes if there is no date in date table or no data in fact this will not work.
Example
CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-1,MONTH)))
Additional question, how do I get the last date of that month? I can't wrap all that in a EOMONTH() ?
Your guess is correct.
Perhaps appending the date table with the missing dates to solve it, if your report is build this way.
A calculated table might come in handy, you can dax a CALENDER function with
DATEADD(LASTDATE(FACT[PDATE]), -6, MONTH), as start date and
DATEADD(LASTDATE(FACT[PDATE]), +3, MONTH) as end date.
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |