Filling the blanks of a Rolling total with Previous non-blank value- DAX Query.

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    HI ,

    I have a dimension Vendor and for this vendor i do not have any Sales Record for 2 Particular months (201901 & 201902)

    I am calculating the Rolling 12 Months using the DatesInPeriod function as below and i see that the values for the Jan and Feb are displaying as Null for the Vendors.

    So i replaced the Null values with Zero but still i am not able to get the Rolling 12 months values and displaying the Zero's for the Jan and Feb.Can someone help me with the DAX Query and Thanks in Advance 🙂

    R12 Total Value:=CALCULATE(IF(ISBLANK(SUM('Fact Total'[Received])),0,SUM('Fact Total'[Received]) ),DATESINPERIOD('Date'[Date],CALCULATE(MAX('Fact Total'[Start_Date])),-12,MONTH ))

    • This topic was modified 3 weeks, 6 days ago by  Steve Dell.
  • Martin Schoombee

    SSCoach

    Points: 18997

    Can you provide more information? A screenshot of the results as well as the monthly numbers and your model would be helpful.

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    Rolling 12 Months Calculations DAX

    • This reply was modified 1 month ago by  Steve Dell.
  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Can you please provide more information, preferably a sample pbix sample file?

    😎

     

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    its a SSAS Tabular project

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    If you Notice the image you will find that the 201807 , 201810, 201812 as Zero's.

    The Rolling 12 Months should calculate as below respectively

    201807 --- 16272.19

    201810 ---  23932.24

    201812 --- 23677.54

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Steve Dell wrote:

    If you Notice the image you will find that the 201807 , 201810, 201812 as Zero's. The Rolling 12 Months should calculate as below respectively 201807 --- 16272.19 201810 ---  23932.24 201812 --- 23677.54

     

    Cannot see any image, only a broken link that gives me a 404 error

    😎

    Since DAX code is "almost" identical in PowerBI, SSAS Tabular and Excel PowerPivot, it is a standard practice to share this kind of problems using the first or the last with sample data.

    😎

     

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    Actual_Date	Received	Vendor
    20180425 0 10256
    20180502 3128.57 10256
    20180614 3103.09 10256
    20180802 3120.19 10256
    20180910 1657.27 10256
    20180910 3107.38 10256
    20180910 3107.38 10256
    20181130 3076.07 10256
    20190121 3123.37 10256
    20190225 3109.67 10256
    20190225 3109.67 10256
    20190225 3109.67 10256
    20190225 -3109.67 10256
  • Jeff Moden

    SSC Guru

    Points: 993644

    Steve Dell wrote:

    Actual_Date	Received	Vendor
    20180425 0 10256
    20180502 3128.57 10256
    20180614 3103.09 10256
    20180802 3120.19 10256
    20180910 1657.27 10256
    20180910 3107.38 10256
    20180910 3107.38 10256
    20181130 3076.07 10256
    20190121 3123.37 10256
    20190225 3109.67 10256
    20190225 3109.67 10256
    20190225 3109.67 10256
    20190225 -3109.67 10256

    I know you don't know but when people say to include some sample data on this site, they mean in a readily consumable form.  Please the the link in my signature line below for one way to do such a thing.  It helps others help you because a whole lot of people on this site actually like to test their solutions before publishing them.  Saves a huge amount of time on both sides.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    --Fact Table with Actaul_Date, Received, Vendor columns
    SELECT '20190225', '3109.67', '10256' UNION ALL
    SELECT '20180614', '3103.09', '10256' UNION ALL
    SELECT '20190121', '3123.37', '10256' UNION ALL
    SELECT '20190225', '-3109.67', '10256' UNION ALL
    SELECT '20181130', '3076.07', '10256' UNION ALL
    SELECT '20180910', '1657.27', '10256' UNION ALL
    SELECT '20190225', '3109.67', '10256' UNION ALL
    SELECT '20180802', '3120.19', '10256' UNION ALL
    SELECT '20180502', '3128.57', '10256' UNION ALL
    SELECT '20180910', '3107.38', '10256' UNION ALL
    SELECT '20190225', '3109.67', '10256' UNION ALL
    SELECT '20180910', '3107.38', '10256' UNION ALL
    SELECT '20180425', '0.00', '10256'

    -- the date demenison has Year, Year_Month Columns
  • Steve Dell

    SSCarpal Tunnel

    Points: 4603

    I even tried to use the below code as example but no luck

    CALCULATE (

    COUNT ( ticketData[CreateDate] ),

    FILTER (

    ALL ( ticketData ),

    ticketData[CreateDate] <= MAX ('Date'[date] )

    )

    )

  • Martin Schoombee

    SSCoach

    Points: 18997

    I think you may be over-complicating the DAX formula. This works for me:

    Rolling 12 Month Total = 
    CALCULATE
    (
    [ReceivedAmount]
    , DATESINPERIOD
    (
    'Date'[Date]
    , LASTDATE('Date'[Date])
    , -12
    , MONTH
    )
    )

    Where [ReceivedAmount] is a simple measure that sums up the the amounts.

    Make sure that your Date dimension is contiguous and also that it is marked as the Date Table in PBI Desktop. This is required for time intelligence to work.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply