Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

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

 
Need Help
  • IamTDR's avatar
    IamTDR
    6 years ago

    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

  • IamTDR's avatar
    IamTDR
    Icon for Responsive Resident rankResponsive Resident
    6 years ago

    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.
     
    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

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

      • IamTDR's avatar
        IamTDR
        Icon for Responsive Resident rankResponsive Resident
        6 years ago

        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.