Ytd, qty, mtd dax

DAX DAX Calculations
1

I wrote some Measures for MTD, QTY and YTD calculations. This is the DAX formula

*YTD Sales = CALCULATE( [*Total Sales], DATESYTD(‘Dates’[Calendar Full Date]))

I simply copied the code and replaces for MTD and QTD. When I bring the meaures into a table only the YTD numbers come in. (See attached Sales Dataset 1 screen shot). However, when I select a month the numbers appear (See attached Sales Dataset 2 screen shot).

sales%20dataset%201
sales%20dataset%2011534×784 81.5 KB
Sales%20Dataset%202
Sales%20Dataset%2021532×815 105 KB

2

When you are having errors like this, please ensure that you post a PBIX file so we can investigate what is going on. A screenshot does not do the trick in these instances. Please post a sample file and I would be happy to assist.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

@jmolina,

The reason this is happening is that when there are no selections made in the month and quarter slicers, the measures have no defined evaluation context against which to calculate a result, and thus return blank. When you select a month, that also defines the quarter you are in, and thus provides the necessary evaluation context for both MTD and QTD.

To prevent this from happening, you need to add some additional logic to your measures telling DAX what to do when there is no selection made in a given slicer. Here’s a short video that explains exactly how to do that:

Hope this is helpful.

  • Brian
4

Yep agreed on both here. A simple demo file can help solve this quicker, and Brian is totally correct here it’s all about context. When you don’t have anything selected it’s doesn’t know what time period to work over, so the selection provides the ‘context’ in which to complete the calculation.

Thanks
Sam