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

Question

Post reply

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

Steve Dell

SSCarpal Tunnel

Points: 4621
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, 4 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: 19088 More actions · Answer 1

Martin Schoombee

SSCoach

Points: 19088

April 19, 2019 at 12:30 pm

#3466202

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

Steve Dell

SSCarpal Tunnel

Points: 4621

April 21, 2019 at 2:16 pm

#3494234

Rolling 12 Months Calculations DAX

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

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

Eirikur Eiriksson

SSC Guru

Points: 182631

April 21, 2019 at 2:41 pm

#3494823

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

😎

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

Steve Dell

SSCarpal Tunnel

Points: 4621

April 22, 2019 at 5:15 am

#3510864

its a SSAS Tabular project

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

Steve Dell

SSCarpal Tunnel

Points: 4621

April 22, 2019 at 6:01 am

#3511500

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

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

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.
"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)
Intro to Tally Tables and Functions

Steve Dell SSCarpal Tunnel Points: 4621 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: 4621 More actions · Answer 10

Steve Dell

SSCarpal Tunnel

Points: 4621

April 25, 2019 at 10:20 am

#3534869

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: 19088 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.