Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Variable in measure doesn't work properly

Hi,

I would like to ask if somebody could help me with problem I have in on of my measures. I'm still learning how to use DAX properly.

I want to calculate last year YTD data by period.  I don't have calendar table and I can't create it because data I use are only with year and period.

I created measure below. (Base is name for last year). This measure works perfectly. Only downside is that I have to change number of second period manually. 

 

 

So, I modified my measure and I created variable to calculate month number based on acutal data. Variable itself works. But when it's in the measure it works only partialy. I didn't notice that there is a problem. When I checked data by total it matched. But when I went deeper into the data it doesn't show some of the data. 

 

It's visible in the picture below that measure with numbers works. But measure with variable skips some accounts. And I don't understand why. I don't have any filter on these accounts. Does somebody know what is the problem?

Or do you know how to calculate this measure differently? Maybe the problem is in the way how I've created this measure. 

 

 

Thanks for help.

 

 

 

 

 

  • I think its likely that for the accounts which are returning blank you don't have data where the variable column is ACT but you do have data where the variable is BASE.

3 Replies

  • I think its likely that for the accounts which are returning blank you don't have data where the variable column is ACT but you do have data where the variable is BASE.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Oh yes! You are right. Thanks a lot. 

    By the way, could you give me some advise how to change it so it could work for last year data? 

    • johnt75's avatar
      johnt75
      Super User

      You should be able to add an additional filter into the CALCULATE to work for whichever year you need, last year would be something like

      'Table'[Year] = YEAR(TODAY()) - 1