Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to do the simplest calculation here. A have a table with a Date Key column and a colum "IsPayday", which is of Type Whole number.
I want to have a running total of the second one and have tried many variations of the formula below, but haven't figured it out:
Thanks for your help!
Solved! Go to Solution.
try
Is Payday running total =
VAR MaxDate =
MAX ( 'Calendar'[Datekey] )
VAR Result =
CALCULATE ( SUM ( 'Calendar'[Is payday] ), 'Calendar'[Datekey] <= MaxDate )
RETURN
Result
Thank for your help.
I decided to change the source data and add ispayday as boolean in the data warehouse. With the field imported into Power BI I used the following variation of your code and got the calculation working:
Is Payday running total =
VAR MaxDate =
MAX ( 'Calendar'[Datekey] )
VAR Result =
CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Datekey] <= MaxDate && 'Calendar'[ispayday] = true )
RETURN
Result
try
Is Payday running total =
VAR MaxDate =
MAX ( 'Calendar'[Datekey] )
VAR Result =
CALCULATE ( SUM ( 'Calendar'[Is payday] ), 'Calendar'[Datekey] <= MaxDate )
RETURN
Result
Thanks for your help @johnt75
I tried it and have this error:
IsPayday is a custom column that I created in the Power Query Editor. Can that be the problem?
You have an extra ] after IsPayday
User | Count |
---|---|
101 | |
90 | |
83 | |
72 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |