Forum Discussion

u201600699's avatar
u201600699
New Member
3 years ago
Solved

Dax Formula

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

 

 

 

 

 

 

 

DAX
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

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