Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm a bit of a learner and practising my skills to make a rainfall viz. Just for fun I have created a paramater that returns the number of days it has rained on the user's birthday, but I have hit a snag and would be sooooo grateful for some help! The rainfall data refers to the rainfall up until 9:00am for the date entered. So if someone enters their birthdate, I need for the date to adjust to the day before (dateadd-1) to calculate based on the rain for the day prior. I have used the dateadd function, but because my parameters are to enter day month and year separately, if someone enters a date that is the 1st of a month, i get a snag because there is no -1 date. I am trying to avoid a whole lot of 'IF' statements that would have to vary depending on the previous month having 31, 30, 29 or 28 days. I just want it to go back one day based on the total of the date entered?
Here is my measure:
Solved! Go to Solution.
@tigersandblues , previous day should work. Can you share sample data and sample output in table format?
So i ended up fixing it by using a calculated columns in my date table (previous date/day), but i would have rather be able to incorporate the previous day calculation in my dax formula:(
@tigersandblues , Create a date from year, month and day .
Date = Date([Year],[Month No],[Day])
Now join this date with date of date table .
You can have this kind of formula for today and yesterday
Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))
Last 2 day
Rolling 2 day = CALCULATE(sum(Table[Number]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),-2,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
So Amitchandak, when you say Create a date from year, month and day . Date = Date([Year],[Month No],[Day]), do you mean i need to create an additional date table, or is that a calculated column or a measure? (Sorry if I sound like a goose!)
instead of separate dropdowns for the day, month and year have you considered using a date slicer or a single date picker control?
I have tried using the date picker, but i can't see an option where it could pick day and month after a certain year. So for the days it rained on someone's birthday, i would need to include all of the day/month possibilites on or after a certain year? PS. I am using a separate date table as recommended. I also previously tried a calculated column in my date table for 'previous day' but couldn't seem to make that work in my measure.
@tigersandblues , previous day should work. Can you share sample data and sample output in table format?
Is this what you mean?
Sample output but includes date
Parameter count rainfall from date | Date |
1 | 14/06/2018 0:00 |
1 | 14/06/2019 0:00 |
1 | 14/06/2020 0:00 |
Rainfall data sample
Year | Month | Day | Rainfall amount (millimetres) |
2018 | 1 | 12 | 0 |
2018 | 1 | 13 | 3.4 |
2018 | 1 | 14 | 22 |
2018 | 1 | 15 | 0 |
So i ended up fixing it by using a calculated columns in my date table (previous date/day), but i would have rather be able to incorporate the previous day calculation in my dax formula:(
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |