Measure is not populating in table rows, only subtotal

[PBIGuyL3]

Hello everyone!

I am in the process of creating a measure for each field that calculates the previous month's data.  It looks as follows: 

Clicks sa360 = Sum(clicks)
Clicks sa360 mom = Calculate([Clicks sa360], ParallelPeriod(Table.Date, -1, Month)

I am using a date table which connects to each fact table with a one to many relationship.  I am then creating a group on the Date field (Date (bins)) which groups the date's by month since that is the unit in ParallelPeriod.  When I created this the first time, everything loaded and I was able to see this:

Figure 1

This is exactly what I wanted to see.  The problem is that the dataset structure needed to change from referencing multiple sources in thin report to building the same dataset in one place, no references (this is a thin report).  When I tried to recreate this on the new version, none of the measures were able to load in the table. 

I troubleshooted everything I could think of but cannot find a way or reason that the measures would populate inside the table on one report, but not on others.  Is this due to the new configuration of the dataset?  Everything seems to be the same in terms of DAX, relationships, and the info is coming from the same source.

Another thing to note:  When the date table has zero relationship with the fact table, both reports will show the total in every row of the table.  When the one to many relationship is made between the date and fact table, the first report loads everything and the second shows blank rows and a total.

Any help is appreciated, thank you!

[Anonymous]

Hi @PBIGuyL3 ,

The created measure performs the calculation of the formula in the filter context. The filter context is the context after the data is filtered after the filter is applied to the table. In short, "ParallelPeriod(Table.Date, -1, Month)" get the last month but the calculation range is filtered as the current row, which is this month. Therefore, the data of other months cannot be obtained and the measure returns a null value. You may consider to modify the formula use ALL() or ALLSELECTED() function to specify the calculation range. For example:

Clicks sa360 mom = Calculate([Clicks sa360], FILTER(ALL('table'), MONTH([date]) = MONTH(SELECTEDVALUE([date]))-1))

Best Regards,

Jay

[amitchandak]

@PBIGuyL3 , for time intelligence you should always prefer Date table and try like

Clicks sa360 mom = Calculate([Clicks sa360], ParallelPeriod('Date'[Date], -1, Month))
or
Clicks sa360 mom = Calculate([Clicks sa360], dateadd('Date'[Date], -1, Month))
or
Clicks sa360 mom = Calculate([Clicks sa360], datesytd(dateadd('Date'[Date], -1, Month)))

if that does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw

https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...