Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
IamTDR
Responsive Resident
Responsive Resident

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

 

CALCULATE(SUM('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('BI CompOrders_Trend_Tbl'[Ship_Date_Month],DATEADD('BI CompOrders_Trend_Tbl'[Ship_Date_Month],6,MONTH),12,MONTH))

 

Any advise I would appreciate it.

Thanks

 
1 ACCEPTED SOLUTION
IamTDR
Responsive Resident
Responsive Resident

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.
 

View solution in original post

5 REPLIES 5
IamTDR
Responsive Resident
Responsive Resident

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.
 

Would be willing to take a look at it but would really appreciate some sample data or the PBIX you are working with. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Any time!


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors