Forum Discussion

setis's avatar
setis
Post Partisan
7 years ago
Solved

When SAMEPERIODLASTYEAR is blank

Dear all, 

 

I am calculating my Numbe of cases on the current year and LY Year to date on a day to day basis.

 

My measure for LY is:

NrCases YTD LY =
IF(ISBLANK([NrCases YTD]);BLANK();
CALCULATE(
[NrCases YTD];
SAMEPERIODLASTYEAR('Date'[Date])
))

My issue is that on some days this measure returns a blank value because there were no cases on that day last year.

 

If the value is blank, I'd like to show the Last non blank value. "5.713" in the screenshot example. 

 

I've tried:

NrCases YTD LY 2= 
VAR CasesLY = CALCULATE(
    [NrCases YTD];
    SAMEPERIODLASTYEAR('Date'[Date])
)
VAR XX = IF(ISBLANK(CasesLY);
         LASTNONBLANK('Date'[Date];CasesLY))
RETURN
IF(ISBLANK([NrCases]);BLANK();
XX
)

But this is not working. 

 

Could somebody point me in the right direction, please?

 

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi setis 

     

    I’ve created the sample as your requested, and use your measures to restore the issue:

    NrCases YTD LY = IF(ISBLANK([NrCases YTD]),BLANK(),
CALCULATE(
[NrCases YTD],
SAMEPERIODLASTYEAR('Table 2'[Date]
)))

    We can add the below new measure to fetch the last non blank date and the volume equaled:

    Measure = var maxd = MAX('Table 2'[Date])
var d = CALCULATE(MAX('Table 2'[Date]),FILTER(ALL('Table 2'),'Table 2'[Date]<=maxd&&[NrCases YTD LY]<>0))
return
IF(ISBLANK([NrCases YTD]),BLANK(),IF(ISBLANK([NrCases YTD LY]),CALCULATE([NrCases YTD LY],FILTER(ALL('Table 2'),'Table 2'[Date]=d)),[NrCases YTD LY]))

    Pbix attached here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EQXhpImx0lhNl2udIt8D3BYBCVkswy5xO09kvwhvFrneYA?e=Ptj3AH

     

     

    Best regards,

    Dina Ye

     

4 Replies

    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      7 years ago

      Hi setis 

       

      I’ve created the sample as your requested, and use your measures to restore the issue:

      NrCases YTD LY = IF(ISBLANK([NrCases YTD]),BLANK(),
CALCULATE(
[NrCases YTD],
SAMEPERIODLASTYEAR('Table 2'[Date]
)))

      We can add the below new measure to fetch the last non blank date and the volume equaled:

      Measure = var maxd = MAX('Table 2'[Date])
var d = CALCULATE(MAX('Table 2'[Date]),FILTER(ALL('Table 2'),'Table 2'[Date]<=maxd&&[NrCases YTD LY]<>0))
return
IF(ISBLANK([NrCases YTD]),BLANK(),IF(ISBLANK([NrCases YTD LY]),CALCULATE([NrCases YTD LY],FILTER(ALL('Table 2'),'Table 2'[Date]=d)),[NrCases YTD LY]))

      Pbix attached here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EQXhpImx0lhNl2udIt8D3BYBCVkswy5xO09kvwhvFrneYA?e=Ptj3AH

       

       

      Best regards,

      Dina Ye

       

    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      7 years ago

      Hi setis 

       

      If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

       

      Best regards,

      Dina Ye

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    There seems to be some mistake in the NrCases YTD measure.  Even if on the same day last year there was  no case, since you are computing a YTD measure, you should not see a blank there.  Share the link from where i can download your PBI file and let me know which month your year starts from - is it January?