Issues with a metric vs LY - Infinity

[elkjoperik]

Hi!

I have a data sett where i have a DATE datebase that is based on DATE - dd.MM.yyyy format. 

I have DATE filters in the report where i can select; YEAR - MONTH - WEEK - DAY

I am trying to to make a "Revenue VS LY" metric where I'm trying to achieve this; 

As you can see, it does't work.

My formula is;

Active filter on page: YEAR = 2017

Formula: CALCULATE(SUM(Revenue)/CALCULATE(SUM(Revenue);'DATE'[YEAR]=2016)

I've also tried to remove the page year filter and specify the year in the formula; 

CALCULATE(SUM(Revenue);'DATE'[YEAR]=2017)/CALCULATE(SUM(Revenue);'DATE'[YEAR]=2016)

But that creates the same scenario. 

I would like to have a running formula where I have the same table as above, and when i select a year, the "revenue vs ly" would change to the filter selected, for example:

If i change the page year filter to 2016, the the formula should calculate 2016 vs 2015, based on if I select YEAR - MONTH - WEEK or DAY,

How would i achieve this ? Thanks!

[elkjoperik · Accepted solution]

Hi,

Thanks for reaching out. 

I found a solution yesterday;  https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...

This solved the issue 😄 

[elkjoperik]

Hi!

I have a data sett where i have a DATE datebase that is based on DATE - dd.MM.yyyy format. 

I have DATE filters in the report where i can select; YEAR - MONTH - WEEK - DAY

I am trying to to make a "Revenue VS LY" metric where I'm trying to achieve this; 

As you can see, it does't work.

My formula is;

Active filter on page: YEAR = 2017

Formula: CALCULATE(SUM(Revenue)/CALCULATE(SUM(Revenue);'DATE'[YEAR]=2016)

I've also tried to remove the page year filter and specify the year in the formula; 

CALCULATE(SUM(Revenue);'DATE'[YEAR]=2017)/CALCULATE(SUM(Revenue);'DATE'[YEAR]=2016)

But that creates the same scenario. 

I would like to have a running formula where I have the same table as above, and when i select a year, the "revenue vs ly" would change to the filter selected, for example:

If i change the page year filter to 2016, the the formula should calculate 2016 vs 2015.

How would i achieve this ? Thanks!

[v-shex-msft]

Hi @elkjoperik,

Share some sample data/pbix file will help us clarify your issue.

Regards,

Xiaoxin Sheng

[Interkoubess]

Hi @elkjoperik,

Please create a calendar table and link it to your data model ( with the date) and then create the following measures:

Current Revenue=Sum([Revenue])
Prev Revenue=calculate([Current Revenue], sameperiodlastyear(Calendar(Dates))

Revenue VS LY=divide([Current Revenue], [Prev Revenue],blank())

Please feel free to give any observations if it does not work or you have any remarks...

Ninter