Forum Discussion
Responsive ResidentHelp w a Date Measure on Power BI Desktop
I am trying to complete a measure where I can look at the units sold, but the time period is a bit different.
I would like to see the units sold after the first 6 months after launch and then see the next 12 months.
So for example, if my product launched 6/1/2018 the measure must ignore the first six months so I want to start summing units beginning Dec 2018 and then continue for next 12 months.
Tried to work up this measure by myself. See below.
Any advise I would appreciate it.
Thanks
Of course after posting on the forum I tried another idea that worked for me.
First I added a column to my dataset that took the ship_date_month and added 6 months to it. Then this measure worked for me.= CALCULATE(SUM('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('BI CompOrders_Trend_Tbl'[Ship_Date_Month],FIRSTDATE('BI CompOrders_Trend_Tbl'[Ship_Date_Month_>6]),11,MONTH))Would still be interested in a measure that would be a little bit easier to use but for now this is working as I tested it in excel over a couple of products.
5 Replies
IamTDROf course after posting on the forum I tried another idea that worked for me.
First I added a column to my dataset that took the ship_date_month and added 6 months to it. Then this measure worked for me.= CALCULATE(SUM('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('BI CompOrders_Trend_Tbl'[Ship_Date_Month],FIRSTDATE('BI CompOrders_Trend_Tbl'[Ship_Date_Month_>6]),11,MONTH))Would still be interested in a measure that would be a little bit easier to use but for now this is working as I tested it in excel over a couple of products.- amitchandak
Super User
I think you have nailed it. The only two difference I can think of one is We could have done +6 months here in this formula. Now + startof month can be replaced with min or max .
Other one using date calendar as we should prefer using date calendar for time intelligence
Rolling 11 till after 6 month = CALCULATE(sum('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('Date'[Date],startOFMONTH(dateadd(Sales[Sales Date],6,month)),11,MONTH))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/ - Greg_Deckler
Community Champion
Would be willing to take a look at it but would really appreciate some sample data or the PBIX you are working with.
- IamTDR
Thanks for the reply, for now I'm going to go with the model as is. I tested a number of products in excel and everything seems to be working as expected.
Thank you for the offer to help.
