Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Set date filter to update with time

I'm doing a calculation to determine what percentage of orders in a period are for a particular product.  It will be plotted against a financial quarter axis, so show growth between this quarter and the previous month.  The report date calculation is robust, accept that as it is.

 

 

ProductTarget = 
VAR ReportDate = IF(ISFILTERED('Backtrack to Previous Month'[End of Previous Month]), FIRSTDATE(ReportingDatePM[ReportingDatePM]), FIRSTDATE(ReportingDate[ReportingDate]))
VAR MonthStart = CALCULATE(STARTOFMONTH('Calendar'[Accounting Date]), FILTER('Calendar', MONTH('Calendar'[Accounting Date]) = MONTH(ReportDate) && YEAR('Calendar'[Accounting Date]) = YEAR(ReportDate)))
VAR NumMonths = SWITCH(MOD(Month(MonthStart), 3),
0, 3,
MOD(MONTH(MonthStart), 3)) + 3
VAR Period =
    DATESINPERIOD ( 'Calendar'[Accounting Date], EDATE(MonthStart, -NumMonths + 1), NumMonths, MONTH )

VAR ProductUnits = CALCULATE([UnitSales], FILTER(Items, Items[PRODUCT_SUBGROUP] = [_TopItem]))
VAR NonProductUnits = CALCULATE([UnitSales], FILTER(Items, Items[PRODUCT_SUBGROUP] <> [_TopItem]))

RETURN

CALCULATE(ProductUnits/(ProductUnits + NonProductUnits), Filter('Calendar', 
DATESINPERIOD('Calendar'[Accounting Date],EDATE(MonthStart, 1 - NumMonths), NumMonths, MONTH)
))

 

 

NumMonths is (a value 1 to 3) + 3, so it varies between 4 and 6.  The number of quarters shown should always be 2.  However, the measure returns results for the full period that we have sales in the system....  A few of the intermediate variables also recorded on cards:

 

 

Values are around what I would expect for each time period, by the way.  Where am I going wrong?

 

Thanks

2 Replies

  • What's the issue here? Have you forgotten to ask the question? 'Cause I can't for the life of me understand it.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi!  I appreciate you clarifying.  I'll update the post with more details.