Forum Discussion

Ben81's avatar
Ben81
Helper I
3 years ago
Solved

Measure in column chart not working

Struggling with what I thought would be a stright forward bit of DAX but it's proviing to be a head sratcher...

I have a column chart with a simple measure that does a count for each sunday and I show this as a YTD week, by week. The DAX looks like this....

CALCULATE(
COUNTROWS(TABLE1),
FORMAT(TABLE1'[DATE],"DDDD") = "Sunday"
)

This works fine, however the actual calculation that I need is to find the difference from the previous Sunday to the current Sunday. 

So I've tried the following...

VAR _dt = SELECTEDVALUE('TABLE1'[DATE])

VAR _CD = 
CALCULATE(
COUNTROWS(TABLE1),
FORMAT(TABLE1'[DATE],"DDDD") = "Sunday"
)

VAR _PD = 
CALCULATE(
COUNTROWS(TABLE1),
'TABLE1'[DATE] = _dt - 7
)

RETURN 
_CD - _PD

This works in a card visual when I select a specific Sunday but not in a column chart. Any ideas how I can achieve this?

 

Thanks,

Ben
 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ben81 ,

     

    You need a calendar like this:

    relationship:

    If you are comparing the changes between two Sundays, try:

     

    Measure 1 = 
VAR _week_1 = CALCULATE(COUNTROWS('Table1'),'Calendar'[Week]=MAX('Calendar'[Week])&&'Calendar'[WeekDay]=7)
VAR _date = CALCULATE(MAX('Calendar'[Date]),'Calendar'[Week]=MAX('Calendar'[Week])&&'Calendar'[WeekDay]=7) - 7
VAR _week_2 = CALCULATE(COUNTROWS('Table1'),'Calendar'[Date]=_date)
VAR _result = _week_1 - _week_2
RETURN
_result

    Then use the 'Calendar' [Date] and [Measure] as axes.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Ben81 ,

     

    You need a calendar like this:

    relationship:

    If you are comparing the changes between two Sundays, try:

     

    Measure 1 = 
VAR _week_1 = CALCULATE(COUNTROWS('Table1'),'Calendar'[Week]=MAX('Calendar'[Week])&&'Calendar'[WeekDay]=7)
VAR _date = CALCULATE(MAX('Calendar'[Date]),'Calendar'[Week]=MAX('Calendar'[Week])&&'Calendar'[WeekDay]=7) - 7
VAR _week_2 = CALCULATE(COUNTROWS('Table1'),'Calendar'[Date]=_date)
VAR _result = _week_1 - _week_2
RETURN
_result

    Then use the 'Calendar' [Date] and [Measure] as axes.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Day of week.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of Table1 to the Date column of the Calendar Table.  Create a slicer and drag Date from the Calendar date - select a date there.  Try these measures

    Sunday count = calculate(countrows(Table1),Calendar[Dow]="Sunday")

    Sunday count last week = calculate([Sunday counbt],datesbetween(Calendar[date],min(calendar[date])-7,min(calendar[date])-7))

    Hope this helps.