Forum Discussion

hansaraneda's avatar
hansaraneda
Icon for Helper I rankHelper I
5 years ago
Solved

help with measure

I need to take a measure and place it in all the rows. to later perform a subtraction between two variables, I can calculate the measure but I cannot replicate it in all the rows or perform the calcu...
Need Help
Tips and Tricks
Tutorial Requests
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    @hansaraneda You can add ALL() if you want it to be calculated on all data/rows.

    previous balance amount 2 - DIVIDE(CALCULATE(SUM(Query1[saldo_anterior])),(CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]-"Entry of Ordinary Activities", ALL())),0)

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

what I need is to create a measure that is the value of "revenue from ordinary activities" last year, this and then subtract it with the values ​​of last year from the rest of the rows.

I'm not sure how to share the example table with you.

attached formula

CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Entry of Ordinary Activities")

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

hansaraneda  Do you have a DimDate table? 

 

You already have this measure:

Revenue of Ordinary Activities = CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Entry of Ordinary Activities")

 

Now create another measure:

Previous Year Revenue of Ordinary Activities = CALCULATE( [Revenue of Ordinary Activities] , DATEADD(DimDate[Date], -12, Month) )

 

Then create the difference measure:

YoY Change Revenue Ordinary Activities = [Revenue of Ordinary Activities] - [Previous Year Revenue of Ordinary Activities]

 

If you don't have a date table, see these links: 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

https://www.excel-university.com/one-click-data-model-date-table/