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

Question

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

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
April 19, 2019 at 10:52 am

#3465349
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 5 years, 7 months ago by Steve Dell.

April 25, 2019 at 10:04 am

This was removed by the editor as SPAM
April 25, 2019 at 10:12 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

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: 4623 More actions · Answer 2

Rolling 12 Months Calculations DAX

This reply was modified 5 years, 7 months ago by Steve Dell.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

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

😎

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 4

Steve Dell

SSCarpal Tunnel

Points: 4623

April 22, 2019 at 5:15 am

#3510864

its a SSAS Tabular project

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 5

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: 182911 More actions · Answer 6

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: 4623 More actions · Answer 7

Actual_DateReceivedVendor
201804250        10256
201805023128.57        10256
201806143103.09        10256
201808023120.19        10256
201809101657.27        10256
201809103107.38        10256
201809103107.38        10256
201811303076.07        10256
201901213123.37        10256
201902253109.67        10256
201902253109.67        10256
201902253109.67        10256
20190225-3109.6710256

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Steve Dell wrote:

Actual_DateReceivedVendor
201804250        10256
201805023128.57        10256
201806143103.09        10256
201808023120.19        10256
201809101657.27        10256
201809103107.38        10256
201809103107.38        10256
201811303076.07        10256
201901213123.37        10256
201902253109.67        10256
201902253109.67        10256
201902253109.67        10256
20190225-3109.6710256

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 9

--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: 4623 More actions · Answer 10

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: 19128 More actions · Answer 11

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.