Skip to main content
Power BI Interview Questions

Practical Challenge 4 DAX

Table of Contents

Consider a scenario where the Date table contains data from January 1, 2020, to December 31, 2022, and the Sales table contains data from January 1, 2020, to November 30, 2022. The Sales table is linked to the Date table based on the date column, with the Date table marked as the primary Date Table. The Sales table has a measure called Net = (SUM(Sales[Net Sales])).

Now, two new measures are created:

  • Sales LMTD = CALCULATE([Net], DATESMTD(DATEADD(‘Date'[Date], -1, MONTH)))
  • Sales PreviousMonth = CALCULATE([Net], PREVIOUSMONTH(‘Date'[Date]))

Here are the questions to consider:

  1. Are the two measures the same?
  2. If October 2022 is selected in a slicer, using the same Date table, will both measures display the same value in the card visuals?
  3. If a date range from September 1, 2022, to October 31, 2022, is selected, will both measures display the same value in the card visuals?
  1. Using CALCULATE([Net], DATESMTD(dateadd(‘Date’[Date], -1, Month))) will display data from the last month up to the selected day of month. On the other hand, CALCULATE([Net], Previousmonth(‘Date’[Date])) will show data for the entire previous month. The distinction becomes apparent when you apply a date filter within the middle of a month or when you compare these measures alongside the dates from the date table.
  2. Yes.
  3. No, the measure using Previousmonth will display data from August 2023. This is because it calculates the previous month based on the first date in the context. In contrast, the measure using DATESMTD will show data from Sep

Add comment