Forum Discussion

Xandman's avatar
Xandman
Helper II
6 years ago
Solved

Multi Row Card Shows Combined Measure

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.

 

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-trend/ba-p/882970


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

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

     

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    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.

  • Xandman's avatar
    Xandman
    Helper II
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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's avatar
        Xandman
        Helper II
        6 years ago

        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!