Forum Discussion

BowerPI's avatar
BowerPI
Regular Visitor
2 years ago
Solved

Variance between months, based on filtered table

Hello!   I'm new to PowerBI so any help would be appreciated 🙂   I have a source data table in PowerBi that looks something like this (Unfortunately can't share actual file due to confidentialit...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi BowerPI ,

    Change the 

    'dcs_test_data_2'[month].[Month]

    into this:

    'dcs_test_data_2'[month].[MonthNo]

    [month].[Month] returns words for each month, and [month].[MonthNo] returns a number for each month


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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

 

 

 

 

Consumption measure: = 
SUM( Data[Consumption] )

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

compare to prev month measure: =
VAR _currentmonth = [Consumption measure:]
VAR _prevmonth =
    CALCULATE (
        [Consumption measure:],
        OFFSET (
            -1,
            ALL ( 'Calendar'[Year-Month sort], 'Calendar'[Year-Month] ),
            ORDERBY ( 'Calendar'[Year-Month sort], ASC )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( _currentmonth ),
        IF (
            NOT ISBLANK ( _prevmonth ),
            FORMAT ( DIVIDE ( _currentmonth - _prevmonth, _prevmonth ), "#0.00%" ),
            FORMAT ( 0, "#0" )
        )
    )

 

  • BowerPI's avatar
    BowerPI
    Regular Visitor
    2 years ago

    Thanks for the reply. I am getting all 0% for my results but it may be due to my formatting of my "date" field. My date formatting is as follows: "Saturday, 30 April, 2022", "Tuesday, 31 May, 2022". How could I update your code above?