Dax measure to display monthly values?

[Ajinkya369]

Hi folks,

The file attached contains data collected Quarter to Date, in other words:

Pbix file: QTD to Monthly Data.pbix - Google Drive 

1. the value for January are the sales for January
2. the value for February are the sales for January + February
3. the value for March are the sales for January + February + March
4. the value for April are for April
5. And so on.....

Thanks

Ajinkya

[Ajinkya369]

Hey @CNENFRNL ,

First of all thanks for replying, I calculated the total for monthly data in excel where the total is : 295990 and I see

that it is not matching with the total of PBI matrix. Could you please help me further..

.Excel TotalPBI Matrix Total

[CNENFRNL · Accepted solution]

@Ajinkya369 , you might want to try this measure to get a different total,

Monthly Data = 
SUMX (
    DISTINCT ( 'QTD Data'[Month] ),
    VAR __current_month = CALCULATE ( MAX ( 'QTD Data'[Month] ) )
    VAR __current_sales = CALCULATE ( SUM ( 'QTD Data'[QTD Sales] ) )
    RETURN
        IF (
            MOD ( MONTH ( __current_month ) , 3 ) = 1,
            __current_sales,
            VAR __prev_month =
                MAXX (
                    FILTER ( ALL ( 'QTD Data'[Month] ), 'QTD Data'[Month] < __current_month ),
                    'QTD Data'[Month]
                )
            RETURN
                __current_sales
                    - CALCULATE ( SUM ( 'QTD Data'[QTD Sales] ), 'QTD Data'[Month] = __prev_month )
        )
)

[CNENFRNL]

Hi, @Ajinkya369 

As the date column consists of incontiguous dates, time intelligence functions, such as DATEADD, ENDOFMONTH etc, don't apply; thus the measure is verbose and fairly complicated. Here's a solution with your original dataset. I attached a pbix file for reference.

Monthly Data = 
VAR __current_sales = SUM ( 'QTD Data'[QTD Sales] )
RETURN
    IF (
        MOD ( MONTH ( MAX ( 'QTD Data'[Month] ) ), 3 ) = 1,
        __current_sales,
        VAR __current = MAX ( 'QTD Data'[Month] )
        VAR __prev =
            MAXX (
                FILTER ( ALL ( 'QTD Data'[Month] ), 'QTD Data'[Month] < __current ),
                'QTD Data'[Month]
            )
        RETURN
            __current_sales
                - CALCULATE ( SUM ( 'QTD Data'[QTD Sales] ), 'QTD Data'[Month] = __prev )
    )

 Without date table

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Nathaniel_C]

Hello @Ajinkya369 ,
Not sure what you are trying to do here.  If you have a calendar in your pbix, then you can use Time Intelligence functions such as TOTALQTD() such as TOTALQTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey])

https://docs.microsoft.com/en-us/dax/totalqtd-function-dax

Let me know if this solves your issue,

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel