Measure to deduct one day from parameter entered

[tigersandblues]

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:  

Parameter count rainfall from date = CALCULATE(

[Count rainfall Days],

FILTER(Station, Station[Station] = "Craigieburn/Epping"),

FILTER('Dates',

Dates[Day] = 'Select Day'[Select Day Value]-1

&& 'Dates'[Month No] = 'Select Month'[Select Month Value]

&& 'Dates'[Year No]>='Select Year'[Year Value]

))

[amitchandak · Accepted solution]

@tigersandblues , previous day should work. Can you share sample data and sample output in table format?

[amitchandak]

@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.

[tigersandblues]

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!)

[lbendlin]

instead of separate dropdowns for the day, month and year have you considered using a date slicer or a single date picker control?

[tigersandblues]

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.

[amitchandak · Accepted solution]

@tigersandblues , previous day should work. Can you share sample data and sample output in table format?

[tigersandblues]

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

Sample workbook 

[tigersandblues · Accepted solution]

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:(

Birthday rain  

Parameter count rainfall from previous date = CALCULATE(

[Count rainfall Days],

FILTER(Station, Station[Station] = "Craigieburn/Epping"),

FILTER('Dates',

'Dates'[Previous day number] = 'Select Day'[Select Day Value]

&& 'Dates'[Previous day month number] = 'Select Month'[Select Month Value]

&& 'Dates'[Previous day year number]>='Select Year'[Year Value]

))