Search code examples
powerbidaxpowerbi-desktopmeasure

Creating a measure totalling different columns depending on date

I have a bar graph visualisation that monitors incoming calls to a call centre by month. Usually, the incoming calls are in the column "s_offered". Due to factors outside my control, any calls received after 2020-07-01 need to be taken from different column "m_adj_offered".

I have a measure to return a boolean if the date is before the above date

m_useadjfigure = if(MIN('Calendar'[Date]) < date(2020,07,01), 0, 1)

I then use the below measure in my visualization which is working fine.

m_magic_entered = if(QueueSummary[m_useadjfigure] = 1, sum(QueueSummary[m_adj_offered]), sum(QueueSummary[s_offered]))

However there is a total in the image which uses the same measure. The months in the visualization are correct. But the sum of all the months does not match the total at the top. I understand why this is happening, because of the m_useadjfigure measure condition and the MIN function. But i am not really sure how i could fix it or how i could rewrite the measure (or write a new one).

The report is dynamic with other years available in a slicer so i cannot apply page-level filters etc.

visualization

In summary, the months on the visualisation are fine. the Total is not. Can anybody offer some DAX suggestions on how to write a measure to calculate the total correctly?

Solution

  • What I will suggest to you is a simple way to resolve your problem and calculate the m_magic_entered measure.

    Use this code:

    m_magic_entered =  
VAR datebar=date(2020,07,01)
RETURN  CALCULATE(sum(QueueSummary[m_adj_offered]),FILTER('Calendar','Calendar'[Date] < datebar) + 
CALCULATE(sum(QueueSummary[s_offered]),FILTER('Calendar','Calendar'[Date] >= 
        datebar)

    You no longer need m_useadjfigure measure.