Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

NA

NA

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous

    I build a sample table to have a test.(Year,Month and Net column are calculated column)

    I think you want to build a waterfall chart to show the diff between the select month in this year and last year.

    Build a DimDate table , Breakdown table and Category table.

     

    DimDate = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month #", MONTH ( [Date] ),
    "Fulldate Month", FORMAT([Date],"MMMM"),
    "Day", DAY ( [Date] ),
    "Datekey",
        FORMAT ( [Date], "yyyy" ) & ""
            & FORMAT ( [Date], "mm" ) & ""
            & FORMAT ( [Date], "DD" )
)

     

     

    Category Table:

    Sort the category by sort column.

    Breakdown Table:

    Build a measure to achieve your goal.

     

    Measure = 
VAR _selectyear =
    SELECTEDVALUE ( DimDate[Year] )
VAR _selectmonth =
    SELECTEDVALUE ( DimDate[Month #] )
VAR _NET =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Net]
    )
VAR _Revenue =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Revenue]
    )
VAR _LYNET =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear - 1
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Net]
    )
VAR _LYRevenue =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear - 1
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Revenue]
    )
RETURN
    IF (
        SELECTEDVALUE ( Category[Sort] ) = 1,
        DIVIDE ( _LYNET, _LYRevenue ),
        DIVIDE ( _NET, _Revenue )
    )

     

     

    Build a slicer by Year and Month# column in DimDate table.

    Build a waterfall chart, default is blank.

    Select month in Slicer.

    For more info to build a waterfall chart column: Power BI & DAX: How to Make Waterfall Charts Work

    You can download the pbix file from this link:  Year over Year Change Subtraction for a Measure %

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , I assume below image, you refer to image posted at the beginning.

     

    This method means diff of data with one year less. So if you do no choose yeas. It will give a different results.

     

    Try these option

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

     

    Year/YTD

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

     

    Power BI — YTD
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    Power BI — MTD
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous Can you post sample data and expected output as text in a table? 

    You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

    Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

     

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous

    I build a sample table to have a test.(Year,Month and Net column are calculated column)

    I think you want to build a waterfall chart to show the diff between the select month in this year and last year.

    Build a DimDate table , Breakdown table and Category table.

     

    DimDate = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month #", MONTH ( [Date] ),
    "Fulldate Month", FORMAT([Date],"MMMM"),
    "Day", DAY ( [Date] ),
    "Datekey",
        FORMAT ( [Date], "yyyy" ) & ""
            & FORMAT ( [Date], "mm" ) & ""
            & FORMAT ( [Date], "DD" )
)

     

     

    Category Table:

    Sort the category by sort column.

    Breakdown Table:

    Build a measure to achieve your goal.

     

    Measure = 
VAR _selectyear =
    SELECTEDVALUE ( DimDate[Year] )
VAR _selectmonth =
    SELECTEDVALUE ( DimDate[Month #] )
VAR _NET =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Net]
    )
VAR _Revenue =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Revenue]
    )
VAR _LYNET =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear - 1
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Net]
    )
VAR _LYRevenue =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = _selectyear - 1
                && 'Table'[Month] = _selectmonth
                && 'Table'[User] = MAX ( 'Table'[User] )
        ),
        'Table'[Revenue]
    )
RETURN
    IF (
        SELECTEDVALUE ( Category[Sort] ) = 1,
        DIVIDE ( _LYNET, _LYRevenue ),
        DIVIDE ( _NET, _Revenue )
    )

     

     

    Build a slicer by Year and Month# column in DimDate table.

    Build a waterfall chart, default is blank.

    Select month in Slicer.

    For more info to build a waterfall chart column: Power BI & DAX: How to Make Waterfall Charts Work

    You can download the pbix file from this link:  Year over Year Change Subtraction for a Measure %

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou