total sales in a month vs total sales in previous year month

[moulshree172]

Hello, I have been trying to put up total sales in a month and also total sales in the last year of that month together. Ex- If i have some sales sum. And I try to match it with March 2018, it gave me an output. But how do I get the total sales for March 2017 with a drillthrough year wise?

I have tried using DAX with YTM and DATEADD  and also SAMEPERIODLASTYEAR. But I couldn't get the values as they are all blank.

Let me know if there is any other way to do it!

Here is a link for my .pbix file on onedrive.

https://ifitech-my.sharepoint.com/:u:/g/personal/moulshree_dhanawade_ifi_tech/ESbVuX9f161BpxelYs1jAI...

[v-qiuyu-msft · Accepted solution]

Hi @moulshree172, 

Please create a measure below: 

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Master Sales Report'[Billing Value ( Rs ) GRAND TOTAL] ),
        FILTER (
            ALL ( 'Master Sales Report' ),
            YEAR ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] )
                = YEAR ( MAX ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] ) ) - 1
                && MONTH ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] )
                    = MONTH ( MAX ( 'Fiscal Calendar'[FullDateAlternateKey] ) )
        )
    ),
    100000,
    1
)

Best Regards,
Qiuyun Yu

[v-qiuyu-msft · Accepted solution]

Hi @moulshree172, 

Please create a measure below: 

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Master Sales Report'[Billing Value ( Rs ) GRAND TOTAL] ),
        FILTER (
            ALL ( 'Master Sales Report' ),
            YEAR ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] )
                = YEAR ( MAX ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] ) ) - 1
                && MONTH ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] )
                    = MONTH ( MAX ( 'Fiscal Calendar'[FullDateAlternateKey] ) )
        )
    ),
    100000,
    1
)

Best Regards,
Qiuyun Yu

[moulshree172]

Hi, the total last year value is coming aroung 13.35K. I have attached a screenshot.Totals Sales v/s last year month sales

[v-qiuyu-msft · Accepted solution]

Hi @moulshree172, 

As there are relationships between tables: Master Sales Report and Fiscal Calendar, when you select any value in slicers, YEAR ( MAX ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] ) ) and MONTH ( MAX ( 'Fiscal Calendar'[FullDateAlternateKey] ) ) is corresponding to the selected slicer values.

The logic of my measure is to find the 'Master Sales Report' table rows when year value is equal to YEAR ( MAX ( 'Master Sales Report'[Calendar Day.Calendar Day Level 01.Key] ) )-1 and month value is equal to MONTH ( MAX ( 'Fiscal Calendar'[FullDateAlternateKey] ) ) then calculate total of the 'Master Sales Report'[Billing Value ( Rs ) GRAND TOTAL].

For your second question, please create a new measure based on the existing measure [Measure]: 

Measure 3 =
VAR t =
SUMMARIZE (
'Master Sales Report',
'Master Sales Report'[Financial Year],
'Fiscal Calendar'[Short Month],
"m", [Measure]
)
RETURN
IF (
ISINSCOPE ( 'Master Sales Report'[Financial Year] ),
[Measure],
SUMX ( t, [m] )
)

Best Regards,
Qiuyun Yu

[moulshree172]

Hi, 

Thank you for the help. Much appreciated.

@v-qiuyu-msft  

[moulshree172]

It is working. But can you explain me what exactly this query does?