Slicer Affecting Data, Get Selected Month Data and Last Year

[powerbinewbie23]

Hi everyone, I'm having some trouble trying to create a total of the revenue from my dataset. I am looking to total the revenue for the slicer selected year and month, and then compare it with the previous year's same month revenue. Here is the DAX query I've run so far - 

Revenue This Year-Month =

CALCULATE(

    SUM('Append1'[qtdamount]),

    'Append1'[scenario] = "Actual",

    Append1[rollup_level_4_name]="Revenue")

Revenue Last Month = 

CALCULATE(

    SUM('Append1'[qtdamount]),

    'Append1'[scenario] = "Actual",

    'Append1'[rollup_level_4_name] = "Revenue",

    'Append1'[fiscalyear] = SELECTEDVALUE('Append1'[fiscalyear]) - 1,

    'Append1'[fiscalmonth] = SELECTEDVALUE('Append1'[fiscalmonth])

)

Revenue Last Month gives no value. Revenue This Year-Month has the fiscalmonth and fiscalyear selected by the user in a splicer. I don't have TI because the data is pulling from another software and I am not allowed to. Also the data is aggregated to month-level so it would cause problems if there was date level duplication? Not sure, but any advice on how to calculate last years same month, or next year's data will be super helpful - dataset is scrubbed and shrunk to relevant columns.

https://jmp.sh/s/ME15lg0ylVqkGt4LIDXf

[Anonymous · Accepted solution]

Hi @powerbinewbie23 

@Ashish_Mathur Thank you very much for your prompt reply. Allow me to offer a different approach here:

Your code seems to be fine, but if it is not outputting the expected results, then try the following code.

Here's some dummy data

“Append1”

Create measures.

Revenue This Year-Month = 
CALCULATE(
    SUM('Append1'[qtdamount]),
    FILTER('Append1',
    'Append1'[scenario] = "Actual" 
    &&
    Append1[rollup_level_4_name]="Revenue"
    )
)

Revenue Last Month = 
CALCULATE(
    SUM('Append1'[qtdamount]),
    FILTER(ALL('Append1'),
    'Append1'[scenario] = "Actual" 
    &&
    'Append1'[rollup_level_4_name] = "Revenue" 
    &&
    'Append1'[ficalyear] = MAX('Append1'[ficalyear]) - 1 
    &&
    'Append1'[ficalmonth] = MAX('Append1'[ficalmonth])
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number
2. Sort the Month name column by the Month number
3. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
4. Create slicer for Year and Month and make a selection
5. Write these measures

Revenue This Year-Month = CALCULATE(SUM('Append1'[qtdamount]),'Append1'[scenario] = "Actual",Append1[rollup_level_4_name]="Revenue")

Revenue in SPLY = calculate([Reenue this Year-Month],sameperiodlastyear(calendar[date]))

Hope this helps.