Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
InterSimi
Helper II
Helper II

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
v-xjiin-msft
Solution Sage
Solution Sage

@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.

View solution in original post

10 REPLIES 10
v-xjiin-msft
Solution Sage
Solution Sage

@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.

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

 

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

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

@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.

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

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

 

 

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 🙂

 

dax 4.jpg

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
Super User
Super User

Need sample data please.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.