Forum Discussion

AndresSalomon's avatar
AndresSalomon
Helper II
8 years ago
Solved

Execute a mesure based on a condition

Hi all, hope you are doing good :) Looking for some help here.    My Fiscal Year starts in Sep to Aug 31st. I have two fact tables: tblForecast and tblActuals. They look like:   tblForecast P...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    8 years ago

    AndresSalomon

     

    I have the same model as you have. Assuming both forecast and actual table are related to the same period table ('date') I use a measure which is basically an IF function:

     

    Monthly Measure = IF(MAX(date[year]) = YEAR(TODAY()) -1, [ACTUALS], IF(MAX(date[month])<MONTH(TODAY())  && MAX(date[year]) = YEAR(TODAY()), [ACTUALS]], [FORECAST]))

    This should give you the monthly data. To get the totals for quarter and year, you need to create a new measure using SUMX(SUMMARIZE('date', 'date[month]), [monthly measure]).

     

    Hope that helps,

     

    Paul.

     

    EDIT : you might have to play around with the statement regarding last year's months; my model is based on a calendar year so it is pretty straightforward.

     

    EDIT #2: I've just realised that the date columns in the IF function need to be wrapped with the MAX function for the measure to work. Sorry about that oversight!

     

AndresSalomon's avatar
AndresSalomon
Helper II
8 years ago

Hi Greg_Deckler! Thanks for watching this post. Your formula worked for you? It's giving me strange values... 

 

I have a Data Model. I mean, the corresponding Look-Up tables (ProjectID, PRNPR, Category) that relate both Data tables (tblActuals, tblForecast). I'm saying because you said that you don't have related tables, and I do. Maybe that is causing this problem.

PaulDBrown's avatar
PaulDBrown
Community Champion
8 years ago

AndresSalomon

 

I have the same model as you have. Assuming both forecast and actual table are related to the same period table ('date') I use a measure which is basically an IF function:

 

Monthly Measure = IF(MAX(date[year]) = YEAR(TODAY()) -1, [ACTUALS], IF(MAX(date[month])<MONTH(TODAY())  && MAX(date[year]) = YEAR(TODAY()), [ACTUALS]], [FORECAST]))

This should give you the monthly data. To get the totals for quarter and year, you need to create a new measure using SUMX(SUMMARIZE('date', 'date[month]), [monthly measure]).

 

Hope that helps,

 

Paul.

 

EDIT : you might have to play around with the statement regarding last year's months; my model is based on a calendar year so it is pretty straightforward.

 

EDIT #2: I've just realised that the date columns in the IF function need to be wrapped with the MAX function for the measure to work. Sorry about that oversight!