Previous Months data column

[Anonymous]

Hi all,

I have a small table summarised of another table. I would like to show the current months data and previous months on the same line/row in the table. I have managed to do this for the previous month using the PREVIOUSMONTH function, however would like to also show the previous months total value on the same line. I have used the following DAX syntax, however it's not worked as expected:

Total Previous Month Value = ROUND(CALCULATE('Summary Table'[Total Calculated Value],PREVIOUSMONTH('Summary by Month'[Previous Month].[Date])),2)

My table is below:

On the Report Date of 01 December 2020 line, I would like the Total Previous Months Value to be blank (as 01 November 2020 is blank), then for 01 January Report Date (01 December 2020 Previous Month), for the column to show 375622.08 etc...

I hope this makes sense and would be very grateful for any advise 🙂

[Anonymous]

Hi,

I am now trying to create an additional table summarised at a higher level (Network Operator) and create the same kind of table as above. I have linked the Calendar to this summarised table and the 'Previous Month' syntax has worked (

Previous Month = PREVIOUSMONTH('Summary by Network Operator'[Report Date])), however when I try and pull through the values from the previous month using the following syntax, it doesn't:

I'm trying to get the values for the previous month by Netowk Operator, any help again (@HashamNiaz) would be grately appreciated! 🙂

Thanks

James

[HashamNiaz]

Hi @Anonymous !

Please check if you have a active relationship between your Date / Calendar table with your (Summary by Network Operator) fact table based on [Report Date] column. Now in your matrix / table visual use Date from Calendar dimension.

Regards,

Hasham

[Anonymous]

Hi @HashamNiaz ,

Yes, there is an active relationship between the two tables:

Thanks for getting back to me! 🙂

James

[HashamNiaz]

Hi @Anonymous !

Did it solved when you placed the [Date] column from Calendar dimesio instead of using [Report Date] from your fact (Summary with Network operator) table.

Regards,

Hasham

[Anonymous]

Hi @HashamNiaz ,

Unfortunately not, I have tried using both the Report Date & Date from the Calendar table and neither work, syntax for both below:

Prev Month Value = CALCULATE(ROUND(SUM('Summary by Network Operator'[Total by Month]),2),DATEADD('Summary by Network Operator'[Report Date],-1,MONTH))

Prev Month Value = CALCULATE(ROUND(SUM('Summary by Network Operator'[Total by Month]),2),DATEADD('Calendar'[Date],-1,MONTH))

Thanks

James

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please update the formula of your measure [Prev Month Value] as below and check whether it works or not:

Prev Month Value = 
VAR _curdate =
    SELECTEDVALUE ( 'Summary by Network Operator'[Report Date] )
VAR _lastmonth =
    IF ( MONTH ( _curdate ) = 1, 12, MONTH ( _curdate ) - 1 )
VAR _year =
    IF ( MONTH ( _curdate ) = 1, YEAR ( _curdate ) - 1, YEAR ( _curdate ) )
RETURN
    CALCULATE (
        ROUND ( SUM ( 'Summary by Network Operator'[Total by Month] ), 2 ),
        FILTER (
            ALLSELECTED ( 'Summary by Network Operator' ),
            YEAR ( 'Summary by Network Operator'[Report Date] ) = _year
                && MONTH ( 'Summary by Network Operator'[Report Date] ) = _lastmonth
        )
    )

If the above one still not working in your scenario, please provide some sample data with Text format and expected result with specific examples and calculation logic. Thank you.

Best Regards

[HashamNiaz]

Hi @Anonymous !

Please use following DAX to get previous month value;

Prev Month Value = CALCULATE([Total Calculated Value], DATEADD(Calendar[Date],-1,MONTH))

You need to have Calendar dimension in your data model & connect your Calendar dimension with your Summary fact table based on Date Key.

Regards,

Hasham

[Anonymous]

Many thanks Hasham, worked a treat!! 🙂