Bagharmin
Board Regular
- Joined
- Nov 22, 2010
- Messages
- 168
I've been using this CUBESET function without issue for a couple of weeks to calculate a sum between two dates, but today it's giving me an #N/A error:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Hours]]","[AllWork].[Group].[GrpOne]","[History].[CurrentlyActive].[Active]",CUBESET("ThisWorkbookDataModel","[AllWork].[Date].&["&TEXT(PrevWk_Start,"YYYY-MM-DDThh:mm:ss")&"]:[AllWork].[Date].&["&TEXT(PrevWk_End,"YYYY-MM-DDThh:mm:ss")&"]"))
It looks like it's due to no data existing with the date given by PrevWk_End (April 3, 2021). As a temporary fix I just changed PrevWk_End to 'PrevWk_End - 1' since data does exist with that date (April 2, 2021), but I can't leave it like that going forward. What's the best way to fix this so that I don't run into this in the future? I'm assuming the same fix will need to be applied to 'PrevWk_Start' in case I run into a situation where I have no data with that date either.
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Hours]]","[AllWork].[Group].[GrpOne]","[History].[CurrentlyActive].[Active]",CUBESET("ThisWorkbookDataModel","[AllWork].[Date].&["&TEXT(PrevWk_Start,"YYYY-MM-DDThh:mm:ss")&"]:[AllWork].[Date].&["&TEXT(PrevWk_End,"YYYY-MM-DDThh:mm:ss")&"]"))
It looks like it's due to no data existing with the date given by PrevWk_End (April 3, 2021). As a temporary fix I just changed PrevWk_End to 'PrevWk_End - 1' since data does exist with that date (April 2, 2021), but I can't leave it like that going forward. What's the best way to fix this so that I don't run into this in the future? I'm assuming the same fix will need to be applied to 'PrevWk_Start' in case I run into a situation where I have no data with that date either.
