DAX assistance: reporting for current month in DAX

[InterSimi]

Hi,

I have the following DAX, but I would like to include only the rows for the backlog for the report 'month'. Basically, I need to reference another table with a single entry which is the date the report was run and it is the month within which the report was run, which I need to report the backlog for.

Any ideas?

Sum values Measure = 

CALCULATE(SUM('Report'[Rpt MRC USD]),'Report'[Metric]="Install") 

+ CALCULATE(SUM('Report'[Rpt MRC USD]),'Report'[Metric]="Backlog") 

+ SUM(ForecastPushPullFromMonth[Reported PushPull Value])

[v-xjiin-msft · Accepted solution]

@InterSimi

=> I need to reference another table with a single entry

Did you mean that in this another table there only exists one record which is the report run date? And you want to get the month of this report run date and use it to filter the backlog of original report?

 Something like this?

new Sum values Measure =
VAR ReportRunTime =
    IF (
        HASONEVALUE ( 'Report Run Time'[Date] ),
        VALUES ( 'Report Run Time'[Date] )
    )
RETURN
    IF (
        MONTH ( ReportRunTime ) = MONTH ( MAX ( Report[Date] ) ),
        CALCULATE ( SUM ( 'Report'[Rpt MRC USD] ), 'Report'[Metric] = "Backlog" ),
        BLANK ()
    )

If above sample does't satisfy your requirement, please kindly share us more detailed information like your source table structure and some sample data with its corresponding desired result.

Thanks,
Xi Jin.

[InterSimi]

I am getting some strange results, here is my DAX (I have renamed a couple of tables):

new Sum Values Measure = 
VAR ReportRunTime = 
    IF(
        HASONEVALUE('Global Report Date'[Metrics Date]),
        VALUES('Global Report Date'[Metrics Date])
    )
RETURN
    IF(
        MONTH(ReportRunTime) = MONTH(MAX('Global Report'[Forecast Date])),
        CALCULATE(SUM('Global Report'[Rpt MRC USD]), 'Global Report'[Metric] = "Backlog"),
        BLANK()

What I am expecting is the SUM of the 'Rpt MRC USD' column if Column 'Metric' = "backlog" and if the 'Forecast date' month = 'metrics date' month.

It appears that certain values are being missed

[InterSimi]

OK, so it looks like there are two issues here:

1. It is also including 2019 (and any other year) so we need to factor in the year
2. The total is calulating the whole backlog figure and not just the one in the DAX

[InterSimi]

One step foreward....

I have managed to include only the MONTH and YEAR, however the total SUM calculation has now disappeared

new Sum Values Measure = 
VAR ReportRunTime = 
    IF(
        HASONEVALUE('Global Report Date'[Metrics Date]),
        VALUES('Global Report Date'[Metrics Date])
    )
RETURN
    IF(
        AND(
            MONTH(ReportRunTime) = MONTH(MAX('Global Report'[Forecast Date])), YEAR(ReportRunTime) = YEAR(MAX('Global Report'[Forecast Date]))),
        CALCULATE(SUM('Global Report'[Rpt MRC USD]), 'Global Report'[Metric] = "Backlog"),
        BLANK()
    )

[v-xjiin-msft]

@InterSimi

Sorry for delayed reply.

Check this:

Sum values Measure =
VAR ReportRunTime =
    IF (
        HASONEVALUE ( 'Report Run Time'[Date] ),
        VALUES ( 'Report Run Time'[Date] )
    )
RETURN
    IF (
        AND (
            MONTH ( ReportRunTime ) = MONTH ( MAX ( Report[Date] ) ),
            YEAR ( ReportRunTime ) = YEAR ( MAX ( Report[Date] ) )
        ),
        CALCULATE (
            SUM ( 'Report'[Rpt MRC USD] ),
            Report[Metric] = "Backlog",
            MONTH ( ReportRunTime ) = MONTH ( Report[Date] ),
            ALLSELECTED ( Report ),
            VALUES ( Report[Metric] )
        ),
        BLANK ()
    )

And for the SUM disappeared, you should know that the the Total in table visual can only calculate the columns not measures. Since we are using a measure to calculate the values, it will not be sumed in table visual's Total.

You can create a new table visual to put Metric and the measure.

Thanks,
Xi Jin.

[InterSimi]

Thanks Xi Jin,

I have run the following code and got the following results:

• I was expecting to see $15.9K as the total
• When I add the "new Sum Values Measure" into the card, it is still showing as blank for some reason

new Sum Values Measure = 
VAR ReportRunTime = 
    IF(
        HASONEVALUE('Global Report Date'[Metrics Date]),
        VALUES('Global Report Date'[Metrics Date])
    )
RETURN
    IF (
        AND (
            MONTH ( ReportRunTime ) = MONTH ( MAX ( 'Global Report'[Forecast Date] ) ),
            YEAR ( ReportRunTime ) = YEAR ( MAX ( 'Global Report'[Forecast Date] ) )
        ),
        CALCULATE (
            SUM ( 'Global Report'[Rpt MRC USD] ),
            'Global Report'[Metric] = "Backlog",
            MONTH ( ReportRunTime ) = MONTH ( 'Global Report'[Forecast Date] ),
            ALLSELECTED ( 'Global Report'),
            VALUES ( 'Global Report'[Metric] )
        ),
        BLANK ()
    )

[InterSimi]

new Sum Values Measure = 
VAR ReportRunTime = 
    IF(
        HASONEVALUE('Global Report Date'[Metrics Date]),
        VALUES('Global Report Date'[Metrics Date])
	)
RETURN
    IF (
        AND (
            MONTH ( ReportRunTime ) = MONTH ( MAX ( 'Global Report'[Forecast Date] ) ),
            YEAR ( ReportRunTime ) = YEAR ( MAX ( 'Global Report'[Forecast Date] ) )
        ),
        CALCULATE (
            SUM ( 'Global Report'[Rpt MRC USD] ),
            'Global Report'[Metric] = "Backlog",
            MONTH ( ReportRunTime ) = MONTH ( 'Global Report'[Forecast Date] ),
            ALLSELECTED ( 'Global Report'),
            VALUES ( 'Global Report'[Metric] )
        ),
        BLANK()
    )

I believe the issue may lie in the logic of the 2nd IF statement. My reason for thinking this, is that it always appears to be "false" and hence always returns BLANK( ).

IF (
        AND (
            MONTH ( ReportRunTime ) = MONTH ( MAX ( 'Global Report'[Forecast Date] ) ),
            YEAR ( ReportRunTime ) = YEAR ( MAX ( 'Global Report'[Forecast Date] ) )

If I replace BLANK( ) with 

MONTH ( ReportRunTime )

I get the returned Month of "1", and the same result for the year.

If I do the same for the following, I get 2019, so I assume this is why it is always giving a false statement

MONTH ( MAX ( 'Global Report'[Forecast Date] ) )

[InterSimi]

I think I have it now.......So, I removed the IF statement and logic

new Sum Values Measure = 
VAR ReportRunTime = 
    IF(
        HASONEVALUE('Global Report Date'[Metrics Date]),
        VALUES('Global Report Date'[Metrics Date])
	)
RETURN
            
        CALCULATE (
            SUM ( 'Global Report'[Rpt MRC USD] ),
            'Global Report'[Metric] = "Backlog",
            MONTH ( ReportRunTime ) = MONTH ( 'Global Report'[Forecast Date] ),
			YEAR( ReportRunTime ) = YEAR( 'Global Report'[Forecast Date] ),
            ALLSELECTED ( 'Global Report'),
            VALUES ( 'Global Report'[Metric] )
        )

and get the following returned, which is what I expected to see....Please let me know if I have done something fundementally silly, as this is a massive learning curve for me here 🙂

[InterSimi]

Hi Xi Jin,

That is exactly what I need, but I need to also SUM to total for that month, so in your example, it would need to SUM the 15 and 20 values and return '35'.

I will run your DAX against my report and see what it returns

[Greg_Deckler]

Need sample data please.