cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
InterSimi Regular Visitor
Regular Visitor

DAX assistance: reporting for current month in DAX

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])

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: DAX assistance: reporting for current month in DAX

@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 ()
    )

22.PNG

 

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.

10 REPLIES 10
Super User
Super User

Re: DAX assistance: reporting for current month in DAX

Need sample data please.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


v-xjiin-msft Super Contributor
Super Contributor

Re: DAX assistance: reporting for current month in DAX

@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 ()
    )

22.PNG

 

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 Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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

InterSimi Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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

 

forecast dax.jpg

 

InterSimi Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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

 

dax 2.jpg

InterSimi Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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()
    )

dax 2.jpg

v-xjiin-msft Super Contributor
Super Contributor

Re: DAX assistance: reporting for current month in DAX

@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 ()
    )

4.PNG

 

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 Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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 ()
    )

dax 3.jpg

InterSimi Regular Visitor
Regular Visitor

Re: DAX assistance: reporting for current month in DAX

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] ) )