[SOLVED] Coursera Power BI project problem with measures

Coursera Power BI project problem with measures

I am trying to finish Power BI course on Coursera, but I do have some problems with measures that they use.

First they asked to create calendar table and it is working:

CalendarTable =  ADDCOLUMNS( CALENDAR(DATE(2020, 1, 1), DATE(2023, 12, 31)), "Year", YEAR([Date]), "Month Number", MONTH([Date]), "Month", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "Weekday", WEEKDAY([Date]), "Day", DAY([Date]) )

Then they are asking to create new column using this code, and it works as well:

Yearly Profit Margin = 'Sales in USD'[Gross Revenue USD] / 'Sales in USD'[Net Revenue USD]

And next I am supposed to create new measures as below, but they do not work:

Quarterly Profit = CALCULATE([Yearly Profit Margin], DATESQTD('CalendarTable'[Date]))

YTD Profit = TOTALYTD([Yearly Profit Margin], 'CalendarTable'[Date])

In both I have the same error, "The value for 'Yearly Profit Margin' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

Why it does not work and how to resolve this?

Solution

I think you are mixing up things when it comes to measures vs. calculated columns. Columns are calculated row by row at the data loading stage and are stored in the table while measures are calculations performed on the data currently loaded into the model and are calculated at query time.

You need to create a measure and not a calculated column :

Yearly Profit Margin Measure = SUM('Sales in USD'[Gross Revenue USD]) / SUM('Sales in USD'[Net Revenue USD])

or :

Yearly Profit Margin Measure = DIVIDE(
    SUM('Sales in USD'[Gross Revenue USD]), 
    SUM('Sales in USD'[Net Revenue USD]),
    BLANK()  //  in case of division by zero
)

and then :

Quarterly Profit = CALCULATE([Yearly Profit Margin Measure], DATESQTD('CalendarTable'[Date]))

YTD Profit = TOTALYTD([Yearly Profit Margin Measure], 'CalendarTable'[Date])