Multi Row Card Shows Combined Measure

[Xandman]

Hi,

I'm not sure why the card always defaulting to a combined year result rather than just the current year.

Here are the measures I use:

Total Accounts = SUMx(fFinStatResults,fFinStatResults[AMOUNT])

Total Revenue = CALCULATE([Total Accounts],dCategoryOrder[CATEGORY]="REVENUE")

dDate = CALENDAR(DATE(year(MIN(fFinStatResults[MONTH])),1,1),DATE(YEAR(MAX(fFinStatResults[MONTH])),12,31))

YEAR = YEAR(dDate[DATE])

Then I place the year on the multi row card and select Last and it always returns the total revenue of all years   rather than the last year available (even though it displays "2019 Last Year").

I also tried Don't Summarize and it returns the oldest year.

[amitchandak · Accepted solution]

In case you have joined on dates you can use

Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

Make sure tables are joined on dates and the calendar table is marked as date.

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Or try

This year using year = CALCULATE(sum(Sales[Sales Amount]),Year(Sales[Sales Date]) =Year(today()))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Xandman]

Thanks for the replies!

This is no year was selected on slicerRelationships2019 was selected on slicer

The year isn't limited to 2018 and 2019, I've just been working on a sample data. If you look at the images attached, dDate has a relationship fFinStatResults[Month] -> this contains 1/31/2018 for example formatted to "Jan 2018", dDate[DATE] is 1/31/2018 12:00 AM

The relationship is confirmed when you select a year on the slicer, my problem is if the slicer is blank, it combines all the years of the data.

[v-lid-msft · Accepted solution]

Hi @Xandman ,

We can create a measure to meet your requirement:

Total Revenue Of Last Year =
VAR LastYear =
    CALCULATE ( YEAR ( MAX ( 'dDate'[Date] ) ), ALLSELECTED ( 'dDate' ) )
RETURN
    CALCULATE (
        [Total Accounts],
        dCategoryOrder[CATEGORY] = "REVENUE",
        FILTER ( ALLSELECTED ( 'dDate' ), YEAR ( 'dDate'[Date] ) = LastYear )
    )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[Xandman]

Thank you for the help!

I now understand solution everyone was giving, I could not just create a universal measure that includes the dates without specifying the range.

I marked both as solutions, hope that counts.

Thanks again!

[amitchandak · Accepted solution]

In case you have joined on dates you can use

Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))

Make sure tables are joined on dates and the calendar table is marked as date.

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Or try

This year using year = CALCULATE(sum(Sales[Sales Amount]),Year(Sales[Sales Date]) =Year(today()))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[AnkitBI]

Hi @Xandman 

To me, it seems the relationship is missing b/w newly created "dDate" Calendar Table and fFinStatResults. Please check after creating a relationship. If issue still occurs, then share PBIX file or sample data.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.