Help with Calculation

[ldwf]

Hello, my PBI model is star schema.  In the fact table, I created two measures in PBI, Current Month Revenue and Prior Month Revenue.   The 'Current' Month and 'Prior' month are determined based on what months are selected in the date slicer, and this works fine and the two measures are correct.  I need to create another field (don't know if I should create a measure or calculated field) that compares the two values for the selected dates and creates a different symbol based on various factors, including what the value of a field in a dimension table is.  Basically, I want to do the following:

if Fact_Table.Prior Month Revenue does not exist, the symbol to show would be 'No Data'

if Fact_Table.Prior Month Revenue is zero, the symbol would be 'N/A'

If Fact_Table.Current Month Revenue is higher than Fact_Table.Prior Month Revenue AND Dim_Forecast_table.Forecast='Strong' then the symbol would be a light green circle symbol

If Fact_Table.Current Month Revenue is higher than Fact_Table.Prior Month Revenue and Dim_Forecast_table.Forecast='Weak' then the symbol would be a dark green circle symbol.

etc

I have tried to break this into pieces by first creating a calculated column indicator to indicate if the prior_month_revenue doesn't exist - Prior_Month_Revenue_Null=calculate(if(isblank('Fact Table'[Prior Month Revenue]), "Y","N"), but when I display the values and the indicator on a report, the indicator says 'Y' for every record.  How would I write this correctly?  Measure or Calculated Field?  How would I write it so as to use the forecast field in the dimension table?

[TomMartens]

Hey @ldwf ,

you have to create a measure that determines the difference, this is because calculated columns can not leverage measures.
After you have created the measure you can use KPI icons to show a symbol, the following article describe how this can be achieved: Conditional Formatting Using Icons In Power BI 🔶 - Excelerator BI

Hopefully, this provides what you are looking for. If not, create a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[ldwf]

Tom, I followed your suggestion and created a measure called 'Difference' that calculates the difference.  if the current month revenue is 0, I show 'N/A', and if the Prior Month Revenue doesn't exist, I show 'No data for prior month', otherwise I show the difference.   I created a sample report and this shows correctly.  However, I am getting back records that don't belong on the report, i.e, records which don't have information for the dates I select in the slicer.  Should I use a DAX formula with var function and filter on the date such that it is equal to the date in the slicer?