Partially syndicated - Outbound Using Previous Month Value for Future

[Max]

I’m having difficulty creating a new column that uses the previous month’s Forecast Percentage for future months. I’m using the measure below but it’s just showing blanks for August onwards. Related measures below

DAU Measure 3 = CALCULATE([Average Users (actuals)],PREVIOUSMONTH(Dates[Date]))

Average Users (actuals) = CALCULATE(DIVIDE([DAU 1M MA],[Total Devices Base - Actuals]))

DAU 1M MA = 
 IF ( MAX ( 'Dates'[Date] ) <= TODAY (),AVERAGEX(
	DATESINPERIOD( Dates[Date],
		LASTDATE( Dates[Date] ),
		-1, MONTH ),
		[Total Users2] ), [DAU Target])

[Harsh]

Hello @Max,

Thank you for posting your query onto the Forum.

Well it’s always difficult to judge the results in the absence of PBIX file. But still I think from the given screenshot provided by you is as follows -

1. The figures portrayed from “Jan - July” are the months that pertains to the current year and therefore based on that it’s showing the Forecast Percentage for those months because in your formula your context is applied as "TODAY() - 1 Month" and therefore it’s giving you the result for the July month as well since we’ve already entered this month.

2. Now for figures for the period from “August - December”, this figures might be pertaining to the last year and therefore it’s not able to showcase the results for those periods since we haven’t entered into those periods. But if you apply filter/slicers for the years such as “Current Year” and “Previous Year” those blank rows will get removed and you’ll see the results only for the period till date.

3. Alternatively, if you’re following the updated “Date” table which is already available in the forum in that case you can use the “IsAfterToday” column to filter out your results and to show the results only for the current period till date.

This similar type of problem was asked few days back onto the Forum by one of the member and it used to show similar sort of results. I’m providing a link of that query as well as working of my PBIX file about how we were able to tackle that problem by incorporating the 3rd option.

Hoping you find this useful and helps you to achieve the desired analysis.

Please feel free to write back in case I’ve missed out on anything.

Thanks & Warm Regards,
Harsh

MTD Measure not working

Hello @Yeriel, Thank you for posting your query onto the Forum. This problem is causing due to the “Date” table i.e. in the “Fact Table” you have the data till the month of June - 2020 and the “Date” table has the dates till 31st December, 2020. Therefore, if you select “2019” from the year slicer you’ll see the values of MTD instead of “Blank”. The reason for this is there is complete data for that particular duration. Below is the screenshot provided for the reference. [DATESMTD - 1] If …

Example Data (1).pbix (334.3 KB)

[Max]

Thank you, Harsh. Update: this is what I’m seeing now:

I’m now seeing the future month value but it’s still not carrying the previous month values over.

Here is the “New Measure” that should theoretically be doing what I want it to…
New Measure = IF ( MAX ( ‘Dates’[Date] ) < Today(),[Forecast Rate(actuals)],[previous month])

[Harsh]

Hello @Max,

I think you’re receiving these results because of date context.

You can try these measure given below -

 `New Measure = 
  IF ( MAX ( FactTable[Date] ) <= Today(),
         [Forecast Rate(actuals)],
               [previous month])`

Thanks & Warm Regards,
Harsh

[Max]

Which table is the FactTable? All my tables are connected to a Date table

[Harsh]

Hello @Max,

Instead of Date Table use the Table in which you’ve data. For e.g. “Sales Table”.

I’m attaching the PBIX file of my working for the reference.Partially Syndicated.pbix (101.6 KB)

Thanks & Warm Regards,
Harsh

[Max]

Still no luck.

[Harsh]

Hello @Max,

If possible can you please provide your PBIX file.

If it’s confidential you can mask your data. I’m providing a link of a video about how you can mask your sensitive data which was created by @BrianJ and then create a sample file of it so that we can have a look at your problem.

Thanks & Warm Regards,
Harsh

How To Mask Sensitive Data In Power BI

[Max]

Uploaded.

Just want to reiterate that the future month % should be the same as the previous months. So in this it should be 10.24% from July - December

[Harsh]

Hello @Max,

Firstly I would like to apologize because the file which you’d sent to me didn’t opened up in my machine due to version that I’m using so I was not able to look at the file.

But I would like to give my last shot in order to get that answer before I rest my case. You can try out the below formula -

Sales Last Purchase = 
VAR PriorDate = CALCULATE( 
                   MAX( FactTable[FactTable Date] ),
                       FILTER( 
            		       ALL( FactTable[FactTable Date] ), 
            				   FactTable[FactTabl Date] < MAX( FactTable[FactTable Date] ) ) )

RETURN
    IF( MAX( Sales[Date] ) <= TODAY() , 
    	[Forecast Rate(actuals)] , 
    		CALCULATE( [previous month],
    			FILTER( 
                   ALL( FactTable[FactTable Date] ),
    				  FactTable[FactTable Date] = PriorDate ) ) ) 

OR

Sales Last Purchase = 
    VAR PriorDate = CALCULATE( 
                       MAX( Dates[Date] ),
                           FILTER( 
                		       ALL( Dates[Date] ), 
                				   Dates[Date] < MAX( Dates[Date] ) ) )

RETURN
  IF( MAX( Dates[Date] ) <= TODAY() , 
     [Forecast Rate(actuals)] , 
         CALCULATE( [previous month],
        	 FILTER( 
                ALL( Dates[Date] ),
                    Dates[Date] = PriorDate ) ) ) 

Just note the FactTable will be table in which your Actuals and Forecasted data is stored combined.

Hoping this is useful. I’m also attaching the PBIX file of my working.

The concept which I’ve applied in this formula is as per the given video.

Compare One Sale Versus The Last Sale (No Time Intelligence) - Advanced DAX...

Thanks & Warm Regards,
Harsh

Partially Syndicated.pbix (104.7 KB)

[EnterpriseDNA]

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

[Max]

Thanks, Harsh–but I think there’s a miscommunication. What I essentially want is this:

Product Name	Month	Scenario	Sales Last Purchase2	previous month
Product 1	January	Actuals	0.1036
Product 1	February	Actuals	0.1022
Product 1	March	Actuals	0.1014
Product 1	April	Actuals	0.1025
Product 1	May	Actuals	0.0995
Product 1	June	Actuals	0.0267
Product 1	July	FY Plan		2.67%
Product 1	August	FY Plan		2.67%
Product 1	September	FY Plan		2.67%
Product 1	October	FY Plan		2.67%
Product 1	November	FY Plan		2.67%
Product 1	December	FY Plan		2.67%
Product 2	January	Actuals	0.0819
Product 2	February	Actuals	0.0797
Product 2	March	Actuals	0.0807
Product 2	April	Actuals	0.082
Product 2	May	Actuals	0.0716
Product 2	June	Actuals	0.0033
Product 2	July	FY Plan		0.33%
Product 2	August	FY Plan		0.33%
Product 2	September	FY Plan		0.33%
Product 2	October	FY Plan		0.33%
Product 2	November	FY Plan		0.33%
Product 2	December	FY Plan		0.33%

The **Sales Last Purchase 2 column for future months won’t have any actuals so it will need to carry the previous values down to the rest of the year.

Currently this is what it shows:

Product Name	Month	Scenario	Sales Last Purchase2	previous month
Product 1	January	Actuals	0.1036
Product 1	February	Actuals	0.1022
Product 1	March	Actuals	0.1014
Product 1	April	Actuals	0.1025
Product 1	May	Actuals	0.0995
Product 1	June	Actuals	0.0267
Product 1	July	FY Plan		9.88%
Product 1	August	FY Plan		9.81%
Product 1	September	FY Plan		9.67%
Product 1	October	FY Plan		9.35%
Product 1	November	FY Plan		9.21%
Product 1	December	FY Plan		9.13%
Product 2	January	Actuals	0.0819
Product 2	February	Actuals	0.0797
Product 2	March	Actuals	0.0807
Product 2	April	Actuals	0.082
Product 2	May	Actuals	0.0716
Product 2	June	Actuals	0.0033
Product 2	July	FY Plan		9.57%
Product 2	August	FY Plan		9.55%
Product 2	September	FY Plan		9.54%
Product 2	October	FY Plan		9.55%
Product 2	November	FY Plan		9.57%
Product 2	December	FY Plan		9.57%

[Harsh]

Hello @Max,

My sincere apologies because since I’m not able to open up a file in my machine it’s really getting difficult for me to evaluate the problem.

But don’t worry I’ve messaged to one of our experts @BrianJ and he said he’ll surely look into your problem and will come with the solution.

Again my sincere apologies for the inconveniences caused to you and was not able help you out due to my system error.

Warm Regards,
Harsh

[Max]

No worries at all, @Harsh!

[Max]

@BrianJ – this is what the data currently looks like (disregard the FY Plan numbers, those are fine–focus on the Actual & Forecast numbers):

Measures:

Previous Month = CALCULATE([DAU Penetration Rate (Actuals)], PREVIOUSMONTH(Dates[Date]))

DAU Penetration Rate (Actuals) = CALCULATE(DIVIDE([Total Users],[Combined Devices]))

Total Users = CALCULATE(SUM ( ‘Landing Pages Final’[Value] ), FILTER ( ‘Landing Pages Final’, ‘Landing Pages Final’[Metric] = “avg_daily_unique_users”))

Combined Devices = CALCULATE(SUM(‘Landing Pages Forecast’[Android Devices Base]))

Relationships

Ultimately I would like the most recent percentage to carry into the future months forecast. In this case the percentages from June - December should be the same.

So for example…

Product Name	Month	Scenario	Total Users	DAU Penetration Rate (Actuals)
Product 1	January	Actuals	1,737,227.25	10.36%
Product 1	February	Actuals	1,703,325.75	10.22%
Product 1	March	Actuals	1,738,796.63	10.14%
Product 1	April	Actuals	1,749,688.13	10.25%
Product 1	May	Actuals	1,743,831.38	10.25%
Product 1	June	Actuals	1,747,805.50	9.00%
Product 1	July	Forecast	1,755,231.63	9.00%
Product 1	August	Forecast	1,761,434.38	9.00%
Product 1	September	Forecast	1,772,202.50	9.00%
Product 1	October	Forecast	1,776,808.25	9.00%
Product 1	November	Forecast	1,784,368.50	9.00%
Product 1	December	Forecast	1,796,997.50	9.00%
Product 2	January	Actuals	2,073,076.00	17.40%
Product 2	February	Actuals	1,559,042.00	16.52%
Product 2	March	Actuals	1,903,785.00	14.22%
Product 2	April	Actuals	2,202,469.00	10.22%
Product 2	May	Actuals	1,869,076.00	11.44%
Product 2	June	Actuals	1,821,604.00	14.65%
Product 2	July	Forecast	2,216,924.00	14.65%
Product 2	August	Forecast	1,724,131.00	14.65%
Product 2	September	Forecast	2,446,829.00	14.65%
Product 2	October	Forecast	2,124,084.00	14.65%
Product 2	November	Forecast	1,571,207.00	14.65%
Product 2	December	Forecast	1,682,086.00	14.65%

[BrianJ]

@Max,

Thanks very much for the additional clarification. I worked on this until late last night – think I was close to a solution and then we lost power due to a violent thunderstorm. Will get back to it after work today, and hopefully have a solution to you this evening.

• Brian

[Max]

Thanks for the update, @BrianJ!

[BrianJ]

@Max,

Still working hard on this one, but it’s proving extremely recalcitrant. Have called in reinforcements to get fresh eyes on the problem. Will continue to keep you posted.

• Brian

[Max]

Thanks, @BrianJ. Looking forward to what you come up with.

[AntrikshSharma]

Thank you for enriching our vocabulary bank meanwhile