Dax Formula

[u201600699]

Hi  im new in power bi. I hava a database with sales and incomes,  columns are de date of process, we evaluate every month the sales of the month,  im tryng to get sales from three months ago and evaluate the persistance of six values over incomes. like the image

i hope someone can help me

[devanshi]

MonthlySales = CALCULATE( SUM('Sales'[Income]),
                           DATESINPERIOD('Sales'[Date of Process], LASTDATE('Sales'[Date of Process]), -1, MONTH)
                           )
Persistence = IF( RANKX(ALL('Sales'[Date of Process]), 'Sales'[Date of Process], , ASC, Dense) > 5,
                                      IF( COUNTROWS( FILTER(
                                             TOPN(6, ALL('Sales'[Date of Process]), 'Sales'[Date of Process], ASC, Dense),                                                                                 'Sales'[Income] = EARLIER('Sales'[Income]) ) ) = 6,
                                                       "Persistent", "Not Persistent" ),
                                             BLANK()
)

[Anonymous · Accepted solution]

Hi @u201600699,

Did you mean to get values from three month ago? If that is the case, you can extract the current date as variable to get the previous value based on current category groups.

3M Ago =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
        ),
        VALUES ( Table[concepto] )
    )

I’m not so clear how the persistence of six values calculated, here is the measure formula to get the difference between current three month ago value and rolling six month fact value averages, you can try it if help:

Diff =
VAR currDate =
    MAX ( Calendar[Date] )
VAR threeMonthAgo =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
        ),
        VALUES ( Table[concepto] )
    )
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Date]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate )
                    && [Date] <= currDate )
        ),
        [Date],
        [concepto],
        "current Value", SUM ( 'Table'[Value] )
    )
VAR AVG =
    AVERAGEX ( summary, [current Value] )
RETURN
    DIVIDE ( threeMonthAgo - AVG, AVG )

Regards,

Xiaoxin Sheng