-1

I'm facing the problem with FY PY calculations.

In my model I have a fact table with 2 connected dimensions:

  • Order Date
  • Posting Date

In the model already exists the measure which calculate the PY value (I use crossfilter to allow using 2 date dimensions in one measure):

IF(ISCROSSFILTERED('Posting Date'),
CALCULATE([Sales Order Value Actual], SAMEPERIODLASTYEAR('Posting Date'[Date])),
CALCULATE([Sales Order Value Actual], SAMEPERIODLASTYEAR('Order Date'[Date])))

The same way I decided to create FY PY calculations however I'm facing problem with showing up years without the data (1900 and 2000), as you can see it below. I can't remove the dates from the date dimension. How can I exclude 1900 and 2000 (these years doesn't hold any data)?

enter image description here

kittysmile
  • 51
  • 6
  • Do you have a date table ? If not, wouldn't it be much better to create one containing all days in your fact table. Then create a relationship between [PostingDate] or [OrderDate] and [Date] column on newly created date table? Then you can determine which relationship to use while creating a measure with calculate() & userelationship() duo.... – Ozan Sen Jul 21 '22 at 13:09
  • Yes, I have 2 date tables- Order and Posting Dates. Unfortunately I cannot create another one because users should be able to use both of them. – kittysmile Jul 21 '22 at 13:23
  • OKAY. No need to create a third date table. If both of them are filtering your fact table at the same time, then It is not a good practice. Maybe you should create 2 different [Sales Amount FY PY] measure, and use OrderDate in one, and PostingDate in another. Is it acceptable for you? But only one of date tables should be active on the model. For example: Measure_01 = CALCULATE([Sales Amount FYPY], USERELATIONSHIP(FactTable[DateColumn],OrderDate[DateColumn]) and the other measure : Measure_02 = CALCULATE([Sales Amount FYPY], USERELATIONSHIP(FactTable[DateColumn],PostingDate[DateColumn]) – Ozan Sen Jul 21 '22 at 13:40
  • I need to create one measure, just like for Previous Year calculation (the measure is working perfectly): IF(ISCROSSFILTERED('Posting Date'), CALCULATE([Sales Order Value Actual], SAMEPERIODLASTYEAR('Posting Date'[Date])), CALCULATE([Sales Order Value Actual], SAMEPERIODLASTYEAR('Order Date'[Date]))) – kittysmile Jul 21 '22 at 13:47

1 Answers1

0

Hej kittysmile. Set your sales amount FY PU= Blank () if the date less then min date in facts.

VAR Result =
    IF(
       ISCROSSFILTERED('Posting Date')
       ,CALCULATE(
                 [Sales Order Value Actual]
                 ,SAMEPERIODLASTYEAR('Posting Date'[Date])
        )
       ,CALCULATE(
                 [Sales Order Value Actual]
                ,SAMEPERIODLASTYEAR('Order Date'[Date])
        )
     )

RETURN
    IF(
       ISCROSSFILTERED('Posting Date')
       ,If(
           MAX(SAMEPERIODLASTYEAR('Posting Date'[Date]) 
                         <
           MIN('factsTable'[Date]),Blank(),result
       ,If(
           MAX(SAMEPERIODLASTYEAR('Order Date'[Date]) 
                         <
           MIN('factsTable'[Date]),Blank(),result
        )
     )
Mik
  • 2,099
  • 1
  • 5
  • 19