previous month not working with hierarchy slicer

[giorgiokatr]

i created 3 columns year quarter and month (they are created from my date column) to use them in hierarchy slicer

when i choose a date from hierarchy  slicer endofmonth works

CALCULATE(SUM('database'[customers]);ENDOFMONTH('database'[date]))

but previous month does not.

 (previousmonth gives the right value eg if i select from hierarchy slicer 02/2016 it shows 29/01/2016 but calculate show 0)

CALCULATE(SUM('database'[customers]);PREVIOUSMONTH('database'[date]))

if i select a month from the default slicer (with the original date) everything works fine.

[BetterCallFrank]

Hi Giorgio,

do you have a gapless date-table in your model?

I.e. you could create a calculated table with the pattern:

Date = CALENDAR ( MIN ( 'database'[date] ), MAX ('database'[date] ) )

such a gapless date table is a prerequisite for the time intelligence to work properly...

HTH,

Frank

[giorgiokatr]

thanks @BetterCallFrank

so i create this calculated table

another problem is that the end of every month in my original database is not 31/01/2016 it is 29/01/2016 (the last data date of every month) is there any solution for this?

[v-huizhn-msft]

Hi @giorgiokatr,

>> Previous month gives the right value eg if i select from hierarchy slicer 02/2016 it shows 29/01/2016 but calculate show 0)

You create a slicer including the data field, and select the Date Hierarchy like the highlighted in yellow background of the first screenshot, right? As I tested, it only return the year when I click the Date Hierarchy shown in second screenshot. How do you select the Month/Year, 02/2016?

 

Based on my sample data as follows, the syntax of your formula is right, and get the desired result.

Create measures using the following formulas. Then I create a slicer including the data, a table including the data and measure. Please see the snapshot below, it will return the last day of previous month, and calculate the sum of value in previous month. Select the 2016/2/4, 22 = 12+10 in January.

end day of previous month = ENDOFMONTH(PREVIOUSMONTH(Table3[Date]))

Total1 = CALCULATE(SUM(Table3[Value]),PREVIOUSMONTH('Table3'[Date]))

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[giorgiokatr]

it works only when i choose date. if i choose date hierarchy and select february it does not work.

i use hirerachy slicer custom visual. maybe thats the problem.

is there any other way to use hierarchy with default slicer?

it gives me the right previous month (29/01/2016) but calculate gives 0.

[BetterCallFrank]

Hi Giorgio,

give this demo PBIX a look, i think it works the way you want, the measures work with dates and date hierarchies:

#Cust LastDate = 
CALCULATE(
  SUM( CntCustomers[CntCustomers] ),
  LASTDATE( 'Date'[Date] )
)

and

#Cust LastDate Previous Month = 
CALCULATE( 
  [#Cust LastDate],
  LASTDATE( PREVIOUSMONTH( 'Date'[Date] ) )
)

HTH,

Frank

[giorgiokatr]

@BetterCallFrank thanks. it works but if u add custom visual date hierarcy it does not. i want to have daye hierarchy so i am forced to use this custom visual. default slicer does not work with hierarchy

[BetterCallFrank]

Giorgio, can you maybe share your PBIX file with all the visuals in place? It should be much easier to help you then.

Frank

[giorgiokatr]

@BetterCallFrank

https://drive.google.com/file/d/0Bw-_YmJ3Q49gaUxtcDB5NTBfSlE/view?usp=drivesdk

[BetterCallFrank · Accepted solution]

Hey @giorgiokatr,

please look at https://dl.dropboxusercontent.com/u/2676210/PreviousMonth.pbix

As I mentioned: prerequisite for time intelligence functions to work properly is a gapless Date table in your model.

I created one in the test-file and adjusted the two measures to use the new table - et voila 🙂

HTH,

Frank

[giorgiokatr]

Thank you @BetterCallFrank ! Yes it works perfect! I already have such a calculated table so i will use it as you adviced!

Thanks!

[v-huizhn-msft]

Hi @giorgiokatr,

I download the file, but they are empty as follows.



Best Regards,
Angelia 

[giorgiokatr]

@v-huizhn-msft yes its blank but in report there are numbers right?