Forum Discussion

khush19's avatar
khush19
Icon for Resolver I rankResolver I
6 years ago
Solved

Push Dataset in powerBI

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?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      khush19
      Icon for Resolver I rankResolver I
      6 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.