Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Monthly difference

Hoping someone can help! Each month I receive a finance file, the total number of rows varies each month. The two columns of interest are financial_ref (which is a unique identifier for each month, though repeated across months) and outcome (which is either Yes or No). A 100% stacked bar chart can show the yes/no% each month (axis = date, value = count(financial_ref), legend = outcome), but what I'd also like is a chart which can show the % difference each month. So if 'No' was 48% in March and 50% in April, I'd like a chart which shows +2% for 'No' for April, being the increase from the previous month (and likewise shows -2% for 'Yes' for April). There's an additional column 'Category' which stakeholders would use to filter any visualisations built. No idea where to start...

Need Help

4 Replies

  • MarcoPessina's avatar
    MarcoPessina
    Resolver IV
    6 years ago

    Hi Anonymous ,

    assuming you have a Date table you can try these measures:

    Total Outcome = CALCULATE(
    COUNTA(Sheet1[Outcome]))
    Total Outcome Prev Month = CALCULATE(
    [Total Outcome],DATEADD('Date'[Date],-1,MONTH))
    % Monthly Change = DIVIDE(
    [Total Outcome] - [Total Outcome Prev Month],[Total Outcome Prev Month], 0)

     

    Hope it helps.

    Cheers,

    Marco

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MarcoPessina ,

       

      that table is exactly what I'd like, but after trying the measures it wasnt giving me right figures. I created a new small table to test it on too, as below,

      ReferenceDateValue
      a01/03/2020Yes
      b01/03/2020No
      c01/03/2020No
      d01/03/2020Yes
      e01/03/2020No
      a01/04/2020Yes
      b01/04/2020No
      c01/04/2020Yes
      d01/04/2020No
      e01/04/2020Yes
      f01/04/2020No

      Getting this result though??

       

      Not sure what I'm missing? Many thanks for your help!

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , you can use time intelligence to get current and last month and then take diff of that

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"8/31")))