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, t...
Need Help
  • MarcoPessina's avatar
    MarcoPessina
    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

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")))