Push Dataset in powerBI

[khush19]

I have a requirement where data from Event Hub is continuously coming to Stream Analytics and I am pushing it to POWERBI with a tumbling window of 15 mins.The Push data set will have data for a week.

The data is Transaction data where I have Amount and Transaction Date

For my requirement i need 3 charts

1. total Amount for today till current time ..SO suppose 04th Nov (6:41 pm) it is $100
2. change in Amount (Total Amount last week same day till same time- total Amount for today till current time) --Suppose last week it is 28 Oct till 6:41 pm it was @$80 so it should show @$20
3. a KPI where it find percentage difference between first two. 20*100/100=20%

as I am using the tumbling window of 15 mins so i wont have transaction exactly till 6:41 but at least Amount where Max(Transaction Date)<= CurrentTime (6:41) for that day .

I am not sure how can I achieve 2nd and 3rd?

[v-juanli-msft · Accepted solution]

Hi @khush19 

If you can use Power BI Desktop to push data and achieve your requirement,

(there is a blog :Create a Power BI Push DataSet using Power BI Desktop w/ PowerBIPS.Tools)

also you are able to create measures in Power BI Desktop,

you could create the following measures:

date1 = DATE(YEAR(MAX('Table'[datetime])),MONTH(MAX('Table'[datetime])),DAY(MAX('Table'[datetime])))

now = NOW()

total_now = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=TODAY()&&'Table'[datetime]<=NOW()))

total_last week = var lastweek=TODAY()-7 return CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=lastweek&&'Table'[datetime]<=NOW()-7))

change = [total_now]-[total_last week]

change% = [change]/[total_now]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[khush19]

Thanks Maggie,

But filter doesn't work for me.it return blank 

.

I tried putting Today() in different measure but no use.

When i use IF to compare date1 with today() it is correct as required but doesn't work in filter. 

[v-juanli-msft]

Hi @khush19 

Do you use the formula as i provided?

If not, please use my formula instead or show me your formula, i think you may miss something.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[khush19]

Yes,I am using your formula:

total_now = CALCULATE(SUM(WeeklyTransactionTable[RealAmount]),FILTER(ALL(WeeklyTransactionTable),WeeklyTransactionTable[date1]=Today()))

Infact when i check adding a measure Check=IF(WeeklyTransactionTable[date1]=Today(),"true","fasle"),it works fine.

it is just that it doesnt work in filter

[khush19]

Hi Maggie,

Thanks for your time and your answer.

Now filter is working.

So earlier I was comparing today() with a column of date which was derived from 

date1 = DATE(YEAR(MAX(WeeklyTransactionTable[MaxTransactionDateTime])),MONTH(MAX(WeeklyTransactionTable[MaxTransactionDateTime])),DAY(MAX(WeeklyTransactionTable[MaxTransactionDateTime])))

so filter was not working.

Then I compared Today() with one of my column of Datetime with hh:mm:ss as 00:00:00

and it worked.

Thanku soooooo much 🙂