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!

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

OK, here is what I am thinking on this one. Create a measure like this:

 

AorF = VAR actual = LOOKUPVALUE(tblActuals[ActualValue],tblActuals[Project],MAX(tblForecast[Project]),tblActuals[Category],MAX(tblForecast[Category]),tblActuals[PNP],MAX(tblForecast[PNP]),tblActuals[Month],MAX(tblForecast[Month]))
RETURN IF(ISBLANK(actual),MAX(tblForecast[ForecastValue]),actual)

If you put this measure into a table visualization along with Project, Category, PNP and Month from your tblForecast, I believe you will get what you are looking to achieve.

 

Also, my 2 tables are NOT related.

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!